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;