1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Split Strings to Column(SQL)

Discussion in 'SQL Server' started by naimish, Jun 30, 2009.

  1. naimish

    naimish New Member

    Joined:
    Jun 29, 2009
    Messages:
    1,046
    Likes Received:
    18
    Trophy Points:
    0
    Occupation:
    Software Engineer
    Location:
    On Earth
    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 a moderator: Jun 30, 2009
  2. shabbir

    shabbir Administrator Staff Member

    Joined:
    Jul 12, 2004
    Messages:
    15,285
    Likes Received:
    364
    Trophy Points:
    83
  3. naimish

    naimish New Member

    Joined:
    Jun 29, 2009
    Messages:
    1,046
    Likes Received:
    18
    Trophy Points:
    0
    Occupation:
    Software Engineer
    Location:
    On Earth
    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. :(
     
  4. shabbir

    shabbir Administrator Staff Member

    Joined:
    Jul 12, 2004
    Messages:
    15,285
    Likes Received:
    364
    Trophy Points:
    83
    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.
     
  5. naimish

    naimish New Member

    Joined:
    Jun 29, 2009
    Messages:
    1,046
    Likes Received:
    18
    Trophy Points:
    0
    Occupation:
    Software Engineer
    Location:
    On Earth
    Will take care of that, Thanks.
     

Share This Page