How to convert comma separated NVARCHAR to table records in SQL Server 2005?

Question

I have a list of ids separated by comma like:

 1,17,25,44,46,67,88

I want to convert them to a table records ( into a temporary table ) like

#tempTable

number_
--------
1
17
25
44
46
67
88

It is possible with a function, a table-valued one ?

Why I want this ? I want to use for INNER JOIN clause (into stored procedure) with another table(s) like as:

SELECT a,b,c FROM T1
INNER JOIN functionNameWhichReturnsTable 
ON functionNameWhichReturnsTable.number_ = T1.a

I cannot use IN because I will use stored procedure which accepts a parameter of type NVARCHAR. That parameter will provide the list of ids.

Thank you

Answer 1

Possible duplicate of separate comma separated values and store in table in sql server.

Please try a precise one from Comma-Delimited Value to Table:

CREATE FUNCTION [dbo].[ufn_CSVToTable] ( @StringInput VARCHAR(8000), @Delimiter nvarchar(1))
RETURNS @OutputTable TABLE ( [String] VARCHAR(10) )
AS
BEGIN

    DECLARE @String    VARCHAR(10)

    WHILE LEN(@StringInput) > 0
    BEGIN
        SET @String      = LEFT(@StringInput, 
                                ISNULL(NULLIF(CHARINDEX(@Delimiter, @StringInput) - 1, -1),
                                LEN(@StringInput)))
        SET @StringInput = SUBSTRING(@StringInput,
                                     ISNULL(NULLIF(CHARINDEX(@Delimiter, @StringInput), 0),
                                     LEN(@StringInput)) + 1, LEN(@StringInput))

        INSERT INTO @OutputTable ( [String] )
        VALUES ( @String )
    END

    RETURN
END
GO

Check the requirement in other way using XML:

DECLARE @param NVARCHAR(MAX)
SET @param = '1:0,2:1,3:1,4:0'

SELECT 
     Split.a.value('.', 'VARCHAR(100)') AS CVS  
FROM  
(
    SELECT CAST ('<M>' + REPLACE(@param, ',', '</M><M>') + '</M>' AS XML) AS CVS 
) AS A CROSS APPLY CVS.nodes ('/M') AS Split(a)

Answer 2

Here's a trick that doesn't need a function or XML.

Basically the string gets transformed into a single insert statement for a temporary table.

The temp table can then be used for further processing.

IF OBJECT_ID('tempdb..#tmpNum') IS NOT NULL
      DROP TABLE #tmpNum;

CREATE TABLE #tmpNum (num int);

DECLARE @TEXT varchar(max) = '1,17,25,44,46,67,88';

DECLARE @InsertStatement varchar(max);
SET  @InsertStatement = 'insert into #tmpNum (num) values ('+REPLACE(@TEXT,',','),(')+');';
EXEC (@InsertStatement);

-- use the temp table 
SELECT * 
FROM YourTable t
WHERE t.id IN (SELECT DISTINCT num FROM #tmpNum);

This method is usable for up to 1000 values.
Because 1000 is the max limit of a row value expression.

Also, as Stuart Ainsworth pointed out.
Since this method uses Dynamic Sql, be wary of code injection and don't use it for strings based on user input.

Side-note

Starting from MS Sql Server 2016, one could simply use the STRING_SPLIT function.

DECLARE @TEXT varchar(max);
SET @TEXT = '1,17,25,44,46,67,88';

SELECT t.* 
FROM YourTable t
JOIN (SELECT DISTINCT CAST(value AS INT) num FROM STRING_SPLIT(@TEXT, ',')) nums
  ON t.id = nums.num;

Answer 3

Completing the answers, you could also use the CSV string to store multiple values in multiple columns:

 --input sql text 
declare @text_IN varchar(max) ='text1, text1.2, text1.3, 1, 2010-01-01\r\n text2, text2.2, text2.3, 2, 2016-01-01'

Split the csv file into rows:

declare @temptable table (csvRow varchar(max))    
declare @DelimiterInit varchar(4) = '\r\n'
declare @Delimiter varchar(1) = '|'
declare @idx int       
declare @slice varchar(max)    

set @text_IN = REPLACE(@text_IN,@DelimiterInit,@Delimiter)


select @idx = 1       
    if len(@text_IN)<1 or @text_IN is null  return       

while @idx!= 0       
begin       
    set @idx = charindex(@Delimiter,@text_IN)       
    if @idx!=0       
        set @slice = left(@text_IN,@idx - 1)       
    else       
        set @slice = @text_IN 

    if(len(@slice)>0)  
        insert into @temptable(csvRow) values(@slice)       

    set @text_IN = right(@text_IN,len(@text_IN) - @idx)       
    if len(@text_IN) = 0 break       
end   

Split rows into columns:

;WITH XMLTable (xmlTag)
AS
(
    SELECT CONVERT(XML,'<CSV><champ>' + REPLACE(csvRow,',', '</champ><champ>') + '</champ></CSV>') AS xmlTag
    FROM @temptable
)

 SELECT RTRIM(LTRIM(xmlTag.value('/CSV[1]/champ[1]','varchar(max)'))) AS Column1,    
        RTRIM(LTRIM(xmlTag.value('/CSV[1]/champ[2]','varchar(max)'))) AS Column2,
        RTRIM(LTRIM(xmlTag.value('/CSV[1]/champ[3]','varchar(max)'))) AS Column3,    
        RTRIM(LTRIM(xmlTag.value('/CSV[1]/champ[4]','int'))) AS Column4,
        RTRIM(LTRIM(xmlTag.value('/CSV[1]/champ[5]','datetime'))) AS Column5
 FROM XMLTable

Answer 4

The following works:

declare @parStoreNo As varchar(8000) = '1,2,3,4'        
CREATE TABLE #parStoreNo (StoreNo INT)-- drop #parStoreNo
declare @temptable VARCHAR(1000) = @parStoreNo
declare @SQL VARCHAR(1000) 
SELECT @SQL = CONVERT(VARCHAR(1000),' select ' + REPLACE(ISNULL(@temptable,' NULL '),',', ' AS Col UNION ALL SELECT ')) 
INSERT #parStoreNo (StoreNo)
EXEC (@SQL)

Answer 5

I am using XML Function as below...

DECLARE @str VARCHAR(4000) = '6,7,7,8,10,12,13,14,16,44,46,47,394,396,417,488,714,717,718,719,722,725,811,818,832,833,836,837,846,913,914,919,922,923,924,925,926,927,927,928,929,929,930,931,932,934,935,1029,1072,1187,1188,1192,1196,1197,1199,1199,1199,1199,1200,1201,1202,1203,1204,1205,1206,1207,1208,1209,1366,1367,1387,1388,1666,1759,1870,2042,2045,2163,2261,2374,2445,2550,2676,2879,2880,2881,2892,2893,2894'

Declare @x XML 

select @x = cast('<A>'+ replace(@str,',','</A><A>')+ '</A>' as xml)

select t.value('.', 'int') as inVal
from @x.nodes('/A') as x(t)

I prefer this because not need to create any separate function and proc. Also I don't have to opt dynamic SQL query which I prefer most. Convert Comma Separated String to Table

Answer 6

DECLARE @str VARCHAR(4000) = '6,7,7,8,10,12,13,14,16,44,46,47,394,396,417,488,714,717,718,719,722,725,811,818,832'

DECLARE @x XML 
select @x = cast('<A>'+ replace(@str,',','</A><A>')+ '</A>' as xml)

select t.value('.', 'int') as inVal
from @x.nodes('/A') as x(t)

Answer 7

Try this code

 SELECT RTRIM(part) as part
    INTO Table_Name
        FROM dbo.splitstring(@Your_Comma_string,',')

splitstring Function is as follows

CREATE FUNCTION dbo.splitstring ( @stringToSplit VARCHAR(MAX) )
RETURNS
 @returnList TABLE ([Name] [nvarchar] (500))
AS
BEGIN

 DECLARE @name NVARCHAR(255)
 DECLARE @pos INT

 WHILE CHARINDEX(',', @stringToSplit) > 0
 BEGIN
  SELECT @pos  = CHARINDEX(',', @stringToSplit)  
  SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)

  INSERT INTO @returnList 
  SELECT @name

  SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
 END

 INSERT INTO @returnList
 SELECT @stringToSplit

 RETURN
END