Split comma separated varchar parameter up into temp table

Question

I have the following stored procedure:

CREATE PROCEDURE myProc
    @nameList varchar(500)
AS
BEGIN
    create table #names (Name varchar(20))

    -- split @nameList up into #names table
END
GO

@nameList would basically look like this:

'John, Samantha, Bob, Tom'

void · Accepted Answer · 2015-04-07T12:06:50.250

4

use convert to XML and cross apply:

  DECLARE @str varchar(50)
  SET @str='John, Samantha, Bob, Tom'

  SELECT names = y.i.value('(./text())[1]', 'nvarchar(1000)')             
  FROM 
  ( 
    SELECT 
        n = CONVERT(XML, '<i>' 
            + REPLACE(@str, ',' , '</i><i>') 
            + '</i>')
  ) AS a 
  CROSS APPLY n.nodes('i') AS y(i)

OUTPUT:

names
-----
John
 Samantha
 Bob
 Tom

EDIT: it's not need to the temp table inside the proc so the proc will be:

CREATE PROCEDURE myProc

    (@nameList varchar(500))

AS
BEGIN

      SELECT names = y.i.value('(./text())[1]', 'nvarchar(1000)')             
      FROM 
      ( 
        SELECT 
            n = CONVERT(XML, '<i>' 
                + REPLACE(@nameList, ',' , '</i><i>') 
                + '</i>')
      ) AS a 
      CROSS APPLY n.nodes('i') AS y(i)
END

but if you want to insert it into a temp table, below is a the sample:

create table #names 
    (
        Name varchar(20)
    )

  DECLARE @str varchar(50)
  SET @str='John, Samantha, Bob, Tom'

  insert into #names
  SELECT names = y.i.value('(./text())[1]', 'nvarchar(1000)')             
  FROM 
  ( 
    SELECT 
        n = CONVERT(XML, '<i>' 
            + REPLACE(@str, ',' , '</i><i>') 
            + '</i>')
  ) AS a 
  CROSS APPLY n.nodes('i') AS y(i)

  select * from #names 
  drop table #names

EDIT 2: the input string may contains some special characters like '<' , '>' , etc it's not standard for names but if the the given string contains them you can remove them by using replace function : replace(@str,'<','')

edited Apr 07 '15 at 12:06

answered Apr 07 '15 at 10:15

void

7,760
3
25
43

Although, shouldn't it say somewhere "select INTO #names"... ? – Apr 07 '15 at 10:25
1

@DeanGrobler, I think there is no need to the temp table there or you want to insert the result into temptable? – void Apr 07 '15 at 10:27
I do want to insert into a temp table. I just used a very simple example for my question. What I'm trying to accomplish in reality here on my side is a bit more complex – Apr 07 '15 at 10:29
@Farhęg what is the additional benefit for `.query('.')` in `n = CONVERT(XML, '' + REPLACE(@str, ',' , '') + '').query('.')`. – ughai Apr 07 '15 at 10:38
@ughai, in this case it's not need to `.query('.')`, removed. thanks for your comment and good luck. – void Apr 07 '15 at 10:45
@Farhęg Please make a note in your answer that this solution will not work for string containing certain characters, for ex. "&" or "<". Try this SET @str='&John, – AlexK Apr 07 '15 at 11:34
@AlexK yes, your comment has good hint. we could use `set @str=replace(@str,'<','')` if we don't want the characters. and by default and by standards the names don't contain the characters you mentioned. – void Apr 07 '15 at 11:40
@Farhęg In a perfect world names don't contain the special characters :) But users often used "&" in alias in WEB. Incidentally, this is not a complete list of symbols resulting in an error. I'm just asking specify this in your answer so that people know what may encounter. That's why I refused to parse strings via XML – AlexK Apr 07 '15 at 12:01

score 1 · Answer 2 · answered Apr 07 '15 at 10:24

With recursive cte:

DECLARE @nameList NVARCHAR(MAX) = 'John, Samantha, Bob, Tom'
SET @nameList = @nameList + ',';

WITH    cte
          AS ( SELECT   SUBSTRING(@nameList, 0, CHARINDEX(',', @nameList)) AS n ,
                        CHARINDEX(',', @nameList) AS i
               UNION ALL
               SELECT   SUBSTRING(@nameList, i + 2,CHARINDEX(',', @nameList, i + 2) - i - 2) ,
                        CHARINDEX(',', @nameList, i + 2)
               FROM     cte
               WHERE    CHARINDEX(',', @nameList, i + 2) > 0
             )
    SELECT  n FROM    cte

Output:

n
John
Samantha
Bob
Tom

score 0 · Answer 3 · answered Apr 07 '15 at 10:16

You can create function and call this function from whereever you want to split:-

create FUNCTION [dbo].[SplitStrings](@nameList varchar(MAX), @Delimiter char(1))       
returns @temptable TABLE (names varchar(MAX))       
as       
begin      
    declare @id int       
    declare @x varchar(8000)       

    select @id = 1       
        if len(@nameList)<1 or @nameList is null  return       

    while @id!= 0       
    begin       
        set @id = charindex(@Delimiter,@nameList)       
        if @id!=0       
            set @x = left(@nameList,@id - 1)       
        else       
            set @x = @nameList

        if(len(@x)>0)  
            insert into @temptable(names) values(@x)       

        set @String = right(@nameList,len(@nameList) - @id)       
        if len(@nameList) = 0 break       
    end   
return 
end;

score 0 · Answer 4 · answered Apr 07 '15 at 11:03

CREATE FUNCTION [dbo].[Split]
(
    @RowData nvarchar(MAX),
    @SplitOn nvarchar(5)
)  
RETURNS @RtnValue table 
(
    Id int identity(1,1),
    Data nvarchar(100)
) 
AS  
BEGIN 
    Declare @Cnt int
    Set @Cnt = 1

    While (Charindex(@SplitOn,@RowData)>0)
    Begin
        Insert Into @RtnValue (data)
        Select 
            Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

        Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
        Set @Cnt = @Cnt + 1
    End

    Insert Into @RtnValue (data)
    Select Data = ltrim(rtrim(@RowData))

    Return
END

Split comma separated varchar parameter up into temp table

4 Answers4

Linked