Go4Expert

Go4Expert (http://www.go4expert.com/)
-   SQL Server (http://www.go4expert.com/forums/sql-server-forum/)
-   -   Split Strings to Column(SQL) (http://www.go4expert.com/forums/split-strings-column-sql-t18261/)

naimish 30Jun2009 08:09

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


shabbir 30Jun2009 09:51

Re: Split Strings to Column(SQL)
 
Please do not paste something from other sites.

http://www.logiclabz.com/sql-server/...nto-table.aspx

naimish 30Jun2009 14:44

Re: Split Strings to Column(SQL)
 
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. :(

shabbir 30Jun2009 14:57

Re: Split Strings to Column(SQL)
 
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.

naimish 30Jun2009 15:30

Re: Split Strings to Column(SQL)
 
Will take care of that, Thanks.


All times are GMT +5.5. The time now is 22:09.