Split Strings to Column(SQL)

naimish's Avatar
Banned
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

Last edited by shabbir; 30Jun2009 at 09:48.. Reason: Code blocks
0
shabbir's Avatar, Join Date: Jul 2004
Go4Expert Founder
Please do not paste something from other sites.

http://www.logiclabz.com/sql-server/...nto-table.aspx
0
naimish's Avatar
Banned
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.
0
shabbir's Avatar, Join Date: Jul 2004
Go4Expert Founder
Its fine but if you have some code which is not yours you should always give credit to the original author when sharing with others.
0
naimish's Avatar
Banned
Will take care of that, Thanks.