Split Strings to Column(SQL)

To split the values in a string using a delimiter (like comma) and put the split values in the form of a table column.
This will be useful when trying to use in clause in SQL.
To split the values in a string using a delimiter (like comma) and put the split values in the form of a table column.
This will be useful when trying to use in clause in SQL.
Note that the Delimiter used in the function can be set to any value as per user's choice. It can also be made an input parameter. User can choose the option according to requirement

Code:

CREATE FUNCTION [dbo].[SplitInputStrToColumn](@InputStr varchar(max))          
returns @outputtable TABLE (items varchar(max))          
as          
begin          
    declare @idx int    
    declare @Delimiter varchar(max)      
    declare @slice varchar(max)          
    set @Delimiter = ','     
    select @idx = 1          
        if len(@InputStr)<1 or @InputStr is null  return          
        
    while @idx!= 0          
    begin          
        set @idx = charindex(@Delimiter,@InputStr)          
        if @idx!=0          
            set @slice = left(@InputStr,@idx - 1)          
        else          
             set @slice = @InputStr          
             
        if(len(@slice)>0)     
            insert into @outputtable(Items) values(@slice)          
     
        set @InputStr = right(@InputStr,len(@InputStr) - @idx)          
        if len(@InputStr) = 0 break          
    end      
return          
end

 

Hi Shabbir, I don't know about other website, this is the code which me and my team has implemented in our application, may be they got it from there, just thought to share it.

 

Will take care of that, Thanks.