CODEWITHSUNDEEP
sqlsql-server
user818700
user818700

Reputation:

Split comma separated varchar parameter up into temp table

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'

Upvotes: 1

Views: 6466

Answers (4)

void
void

Reputation: 7890

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,'<','')

Upvotes: 4

Arun Gairola
Arun Gairola

Reputation: 884

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

Upvotes: 0

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

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

Upvotes: 1

Rahul Tripathi
Rahul Tripathi

Reputation: 172458

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;

Upvotes: 0

Related Questions