none
How to split comma delimited string?

    Question

  • Hi,

     

    I have a parameter called Id in my SP which will be of nvarchar data type and i'm going to get the multiple ids at a time seperated by commas in that parameter from the application. Now my requirement is to update a table with these comma seperated ids in seperate rows.

     

    For example, if i have 2 parameters called Id1 and Id2. Id1 will contain only one value and will be of int data type and Id2 will be of nvarchar data type as i can get multiple ids delimited by a comma from the application. Suppose Id1 = 1 and Id2 = '1,2,3,4'. Then I have to update id2 in the tables seperately like wherever Id1 is '1' i need to update Id2 column for 4 rows with the value 1, 2, 3, 4 respectively in different rows.

     

    Could you please tell me how can i do this in T-SQL? How can i split the data of parameter Id2 in 4 different rows?

     

    Please let me know if you did not understand the question.

     

    Thanks,

    Deepti

    Wednesday, October 08, 2008 5:11 AM

Answers

  • Hi:

     

       Here is a solution of 2005:

     

    Code Snippet

    DECLARE @S varchar(max),
      @Split char(1),
      @X xml

    SELECT @S = '1,2,3,4,5',
      @Split = ','

    SELECT @X = CONVERT(xml,'<root><s>' + REPLACE(@S,@Split,'</s><s>') + '</s></root>')

    SELECT [Value] = T.c.value('.','varchar(20)')
    FROM @X.nodes('/root/s') T(c)

     

     

    Hope this helps.
    Wednesday, October 08, 2008 5:38 AM
  •  deeptideshpande wrote:

    Hi,

     

    I got a similar function which is like this:

     

    CREATE FUNCTION dbo.fnSplit(
        @sInputList VARCHAR(8000) -- List of delimited items
      , @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
    ) RETURNS @List TABLE (item VARCHAR(8000))
    
    BEGIN
    DECLARE @sItem VARCHAR(8000)
    WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
     BEGIN
     SELECT
      @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
      @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))
     
     IF LEN(@sItem) > 0
      INSERT INTO @List SELECT @sItem
     END
    
    IF LEN(@sInputList) > 0
     INSERT INTO @List SELECT @sInputList -- Put the last item in
    RETURN
    END
    GO
    

     

    Now when i use this in my select statement, i get data in a tabular structure as shown below:

     

    select * from dbo.fnSplit('1,22,333,444,,5555,666', ',')

    Results

    Item

    1
    22
    333
    444
    5555
    666

     

    Now my requirement is to insert this value in a table which has 2 columns ID1 and ID2.

    At any point of time ID1 will be an int value(only one id in that parameter) and ID2 can have multiple ids delimited by comma. With the help of above split function i can split values of ID2 into 6 different ids but i need to insert the table in the following way:

     

    ID1     ID2

    1        1
    1        22
    1        333
    1        444
    1        5555
    1        666

     

    How can i insert the data in the above mentioned fashion.

     

    Thanks,

    Deepti

     

    Like this:

     

    Code Snippet

    select 1 as id,* from dbo.fnSplit('1,22,333,444,,5555,666', ',')

     

     

    Wednesday, October 08, 2008 9:03 AM
  • The XML data type makes the processing much simpler. It is available only in sql server 2005/2008. If you are SQL server 2000, you should use the other option provided.

     

    To read values from an xml data type, you need to specify the path that specifies the location of the value. "/root/s" refers to the location where the value is located.

     

    If you want to know more about XQuery, I would suggest reading Book online and then have a look at the tutorials given here: http://jacobsebastian.blogspot.com/2008/06/xquery-labs-collection-of-xquery-sample.html

    Wednesday, October 08, 2008 6:53 AM

All replies

  • Hi:

     

       Here is a solution of 2005:

     

    Code Snippet

    DECLARE @S varchar(max),
      @Split char(1),
      @X xml

    SELECT @S = '1,2,3,4,5',
      @Split = ','

    SELECT @X = CONVERT(xml,'<root><s>' + REPLACE(@S,@Split,'</s><s>') + '</s></root>')

    SELECT [Value] = T.c.value('.','varchar(20)')
    FROM @X.nodes('/root/s') T(c)

     

     

    Hope this helps.
    Wednesday, October 08, 2008 5:38 AM
  • Here is the solution in 2000:

     

    Code Snippet

    create function f_split(@SourceSql varchar(8000),@StrSeprate varchar(10))
    returns @temp
    table(a varchar(100))
    begin   
     declare @i int   
     set @SourceSql=rtrim(ltrim(@SourceSql)) 
     set @i=charindex(@StrSeprate,@SourceSql)   
     while @i>=1   
     begin       
      insert @temp values(left(@SourceSql,@i-1))       
      set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)       
      set @i=charindex(@StrSeprate,@SourceSql)   
     end   
     if @SourceSql<>'\'      
     insert @temp values(@SourceSql)   
     return
    end
    go

    select * from dbo.f_split('1,2,3,4,5',',')
    go

     

     

    Hope this helps.
    Wednesday, October 08, 2008 5:42 AM
  • Could you please explain this code as i'm quite new to t-sql programming. Why do we need to use xml datatype here and what is this "/root/s"?

     

    Thanks,

    Deepti

     

    Wednesday, October 08, 2008 5:46 AM
  • The XML data type makes the processing much simpler. It is available only in sql server 2005/2008. If you are SQL server 2000, you should use the other option provided.

     

    To read values from an xml data type, you need to specify the path that specifies the location of the value. "/root/s" refers to the location where the value is located.

     

    If you want to know more about XQuery, I would suggest reading Book online and then have a look at the tutorials given here: http://jacobsebastian.blogspot.com/2008/06/xquery-labs-collection-of-xquery-sample.html

    Wednesday, October 08, 2008 6:53 AM
  • Is there no other way of solving it?

     

    Wednesday, October 08, 2008 6:55 AM
  • Another option is to create a scalar function. If you google for "tsql function split string" you will get a number of functions already written. This is just on of them: http://blogs.vbcity.com/hotdog/archive/2008/06/04/9085.aspx (I have not used it, but see if this gives you the desired results).

     

    Also search in this forum, there were many threads previously discussed this topic and several solutions were provided.

     

    Wednesday, October 08, 2008 6:59 AM
  • Hi,

     

    I got a similar function which is like this:

     

    CREATE FUNCTION dbo.fnSplit(
        @sInputList VARCHAR(8000) -- List of delimited items
      , @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
    ) RETURNS @List TABLE (item VARCHAR(8000))
    
    BEGIN
    DECLARE @sItem VARCHAR(8000)
    WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
     BEGIN
     SELECT
      @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
      @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))
     
     IF LEN(@sItem) > 0
      INSERT INTO @List SELECT @sItem
     END
    
    IF LEN(@sInputList) > 0
     INSERT INTO @List SELECT @sInputList -- Put the last item in
    RETURN
    END
    GO
    

     

    Now when i use this in my select statement, i get data in a tabular structure as shown below:

     

    select * from dbo.fnSplit('1,22,333,444,,5555,666', ',')

    Results

    Item

    1
    22
    333
    444
    5555
    666

     

    Now my requirement is to insert this value in a table which has 2 columns ID1 and ID2.

    At any point of time ID1 will be an int value(only one id in that parameter) and ID2 can have multiple ids delimited by comma. With the help of above split function i can split values of ID2 into 6 different ids but i need to insert the table in the following way:

     

    ID1     ID2

    1        1
    1        22
    1        333
    1        444
    1        5555
    1        666

     

    How can i insert the data in the above mentioned fashion.

     

    Thanks,

    Deepti

    Wednesday, October 08, 2008 8:52 AM
  •  deeptideshpande wrote:

    Hi,

     

    I got a similar function which is like this:

     

    CREATE FUNCTION dbo.fnSplit(
        @sInputList VARCHAR(8000) -- List of delimited items
      , @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
    ) RETURNS @List TABLE (item VARCHAR(8000))
    
    BEGIN
    DECLARE @sItem VARCHAR(8000)
    WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
     BEGIN
     SELECT
      @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
      @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))
     
     IF LEN(@sItem) > 0
      INSERT INTO @List SELECT @sItem
     END
    
    IF LEN(@sInputList) > 0
     INSERT INTO @List SELECT @sInputList -- Put the last item in
    RETURN
    END
    GO
    

     

    Now when i use this in my select statement, i get data in a tabular structure as shown below:

     

    select * from dbo.fnSplit('1,22,333,444,,5555,666', ',')

    Results

    Item

    1
    22
    333
    444
    5555
    666

     

    Now my requirement is to insert this value in a table which has 2 columns ID1 and ID2.

    At any point of time ID1 will be an int value(only one id in that parameter) and ID2 can have multiple ids delimited by comma. With the help of above split function i can split values of ID2 into 6 different ids but i need to insert the table in the following way:

     

    ID1     ID2

    1        1
    1        22
    1        333
    1        444
    1        5555
    1        666

     

    How can i insert the data in the above mentioned fashion.

     

    Thanks,

    Deepti

     

    Like this:

     

    Code Snippet

    select 1 as id,* from dbo.fnSplit('1,22,333,444,,5555,666', ',')

     

     

    Wednesday, October 08, 2008 9:03 AM
  • Thanks a lot!!! Smile It worked.

     

    Thanks,

    Deepti

     

    Wednesday, October 08, 2008 9:06 AM
  • Hi,
     I also have the same problem, I need to insert comma seperated values in one column and another value that is same for all rows in another column. How you solved it.

    i m using this code:

    DECLARE @S varchar(max),
      @Split char(1),
      @X xml

    SELECT @S = '1,2,3,4,5',
      @Split = ','

    SELECT @X = CONVERT(xml,'<root><s>' + REPLACE(@S,@Split,'</s><s>') + '</s></root>')

    SELECT [Value] = T.c.value('.','varchar(20)')
    FROM @X.nodes('/root/s') T(c)

     

    can you tell what changes you made in it.

     

     Medha

    "Free advice is worth the price"

    Thursday, April 23, 2009 10:35 AM
    • Edited by Naomi NModerator Sunday, November 20, 2011 3:26 AM removed Avatar reference
    Saturday, November 19, 2011 2:59 AM
  •  Thanks, deepti

    it's realy nice function.

    Thursday, February 16, 2012 9:41 AM
  • i have one question how can i pass a table and its field value in this storeprocedure like my table has 

    class      class_subject 

    1          5,6,7,8

    2               6,8,7,1

    so how can i pass the class_subject in this . I want to split the class_subject value 

    Abhishek

    Saturday, June 23, 2012 10:00 AM
  • First, it's always better to start a new thread than adding to a thread which is several years old.

    Start here:
    http://www.sommarskog.se/arrays-in-sql-2005.html#tablelists
    if you don't get everything, start reading the article from the beginning.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, June 23, 2012 12:16 PM
  • I Stumbled across this thread and thought i would share my proc that drops temp tables with the world.  

    It makes working with new queries painless.  (no need to drop the table if the query fails, and no need to create multiple tests to see if temp tables exist)

    Here is the proc:

    create proc [dbo].[p_DropTempTables]
    (@String varchar(500))
    as
    
    declare  
      @i int
    , @1 int
    , @2 int
    , @TT varchar(50)
    
    set nocount on
    set @i = 0
    set @1 = 0
    set @2 = 0
    set @String = ltrim(rtrim(@String))
    set @String =	case 
    				when left(@String,1) like ','
    				then @String
    				else @String +','
    				end
    
    While @i < len(@String) - len(replace(@String,',',''))
    begin
    	set @TT = ltrim(rtrim(right(left(@string,charindex(',',@String,@1)-1),(charindex(',',@String,@1)-1)- case @i when 0 then 0 else (charindex(',',@String,@2)) end)))
    	Set @2 = @1
    	Set @1 = charindex(',',@String,@1) + 1
    	Set @i = @i + 1
    
    	if object_id('tempdb..'+@TT) is not null
    	begin
    		exec('drop table '+@TT)
    	end
    end 

    and here you can test it:

    exec dbo.p_DropTempTables '#Temp,#Temp2'
    
    create table #Temp
    (ID INT, Descrip varchar(4))
    
    insert into #Temp
    select 1 as id, 'This' as descrip
    union 
    select 2 as id, 'That' as descrip
    union 
    select 3 as id, 'What' as descrip
    
    
    create table #Temp2
    (ID INT, Descrip varchar(4))
    
    insert into #Temp2
    select 1 as id, 'This' as descrip
    union 
    select 2 as id, 'That' as descrip
    union 
    select 3 as id, 'What' as descrip
    
    
    
    
    
    select * from #Temp
    select * from #Temp2
    
    --drop table #Temp, #Temp2

    Friday, June 29, 2012 3:37 AM
  • SIMPLY SUPERB CODE!!!

    Thanks

    Thursday, October 04, 2012 9:39 AM
  • The below code snippet is splitting the string and displaying on single column in different rows.  

    DECLARE @S varchar(max),

      @Split char(1),
      @X xml

    SELECT @S = '1,2,3,4,5',
      @Split = ','

    SELECT @X = CONVERT(xml,'<root><s>' + REPLACE(@S,@Split,'</s><s>') + '</s></root>')

    SELECT [Value] = T.c.value('.','varchar(20)')
    FROM @X.nodes('/root/s') T(c)

    But I wanted them in separate columns.  Can you please help me on that.

    Col1 Col2 Col3 Col4 Col5

    1 2 3     4 5

    Thanks,

    Nandhakumar.N


    Tuesday, December 18, 2012 6:02 PM
  • Is it possible to split the null values for saving.?
    like

    select data from split('1,2,3,4,,6',',')

    to  like this
    Data
    1

    2

    3

    4

    NULL

    6

    is this possible with this function ?

    Friday, March 22, 2013 1:02 PM
  • I h just placed a function that I using since long. 

    http://www.thesqlpost.blogspot.com/2013/09/sql-server-user-defined-function-to.html

    Ashish.

    • Proposed as answer by Ashish.Upadhyay Tuesday, September 24, 2013 10:19 PM
    Tuesday, September 24, 2013 10:19 PM
  •  deeptideshpande wrote:

    Hi,

     

    I got a similar function which is like this:

     

    CREATE FUNCTION dbo.fnSplit(
        @sInputList VARCHAR(8000) -- List of delimited items
      , @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
    ) RETURNS @List TABLE (item VARCHAR(8000))
    
    BEGIN
    DECLARE @sItem VARCHAR(8000)
    WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
     BEGIN
     SELECT
      @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
      @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))
     
     IF LEN(@sItem) > 0
      INSERT INTO @List SELECT @sItem
     END
    
    IF LEN(@sInputList) > 0
     INSERT INTO @List SELECT @sInputList -- Put the last item in
    RETURN
    END
    GO
    

     

    Now when i use this in my select statement, i get data in a tabular structure as shown below:

     

    select * from dbo.fnSplit('1,22,333,444,,5555,666', ',')

    Results

    Item

    1
    22
    333
    444
    5555
    666

     

    Now my requirement is to insert this value in a table which has 2 columns ID1 and ID2.

    At any point of time ID1 will be an int value(only one id in that parameter) and ID2 can have multiple ids delimited by comma. With the help of above split function i can split values of ID2 into 6 different ids but i need to insert the table in the following way:

     

    ID1     ID2

    1        1
    1        22
    1        333
    1        444
    1        5555
    1        666

     

    How can i insert the data in the above mentioned fashion.

     

    Thanks,

    Deepti

     

    Like this:

     

    Code Snippet

    select 1 as id,* from dbo.fnSplit('1,22,333,444,,5555,666', ',')

     

     

    Great!!!! Wonderfull!!!... Thanks a lot.
    Monday, February 24, 2014 3:20 PM