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

possible duplicate of [Turning a Comma Separated string into individual rows](http://stackoverflow.com/questions/5493510/turning-a-comma-separated-string-into-individual-rows) — Ravindra Gullapalli, Mar 23 '13 at 10:25
possible duplicate of [Split string in SQL](http://stackoverflow.com/questions/2647/split-string-in-sql) — Aleksandr Fedorenko, Mar 23 '13 at 10:52

score 25 · Accepted Answer · edited May 23 '17 at 11:53

25

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)

edited May 23 '17 at 11:53

Community

1
1

answered Mar 23 '13 at 10:26

TechDo

18,398
3
51
64

The XML split is more than 3 times slower than the loop one! – Jitendra Gupta Aug 29 '17 at 11:19
XML split will be more faster then loop one if you have thousands of values in a comma separated string i.e. in @param – Gurunadh Oct 25 '17 at 14:43
To handle spaces in list, e.g. 1, 2, 3, modify section to `SET @String = LTRIM(RTRIM(LEFT(@StringInput, ISNULL(NULLIF(CHARINDEX(@Delimiter, @StringInput) - 1, -1), LEN(@StringInput)))))` – Lomak Jun 15 '18 at 14:51
ufn_CSVToTable works fine only for such string 1,3,5,7,33,22 but we try such strings like 'K2SQL:K2Admin,K2SQL:K2Admin' then its output does not work – Asad Naeem Jun 25 '20 at 09:23
the method which is written here that is working fine in all cases. https://www.sqlshack.com/converting-commas-or-other-delimiters-to-a-table-or-list-in-sql-server-using-t-sql/ – Asad Naeem Jun 25 '20 at 09:25

LukStorms · Answer 2 · 2020-01-30T10:53:29.460

7

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;

edited Jan 30 '20 at 10:53

answered Mar 24 '16 at 10:59

LukStorms

28,916
5
31
45

Try this line : DECLARE @TEXT varchar(max) = '1,17,25,44,46,67,88);DELETE FROM #tempTable WHERE (1=1'; SQL injection is possible with this method, though not likely. – Stuart Ainsworth Mar 24 '16 at 11:05
I'm sorry; I think you misunderstood. I put a DELETE statement into a text string, which would be executed by your exec statement. THat's a classic form of SQL injection; with sufficient privilege, someone could do all kinds of mischief (like drop a database, create users)... – Stuart Ainsworth Mar 24 '16 at 11:21
Again, it's not likely, but this is a design pattern to avoid. – Stuart Ainsworth Mar 24 '16 at 11:22
Yes, I agree that when you use EXEC that code injection is a considerable risk. But only when you use it with user input. Which isn't always the case. Thanks for pointing that out. – LukStorms Mar 24 '16 at 11:34
Well, to be honest, you can't use dynamic SQL with a user function, either (the crux of the original question) :) – Stuart Ainsworth Mar 24 '16 at 12:17

score 0 · Answer 3 · answered Jun 23 '16 at 18:55

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

score 0 · Answer 4 · edited Apr 11 '18 at 11:39

0

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)

edited Apr 11 '18 at 11:39

cfnerd

3,658
12
32
44

answered Apr 11 '18 at 10:55

Petrus Norlin

1

score 0 · Answer 5 · answered Oct 18 '19 at 08:15

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

score 0 · Answer 6 · edited Dec 04 '19 at 15:50

0

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)

edited Dec 04 '19 at 15:50

blerontin

2,892
5
34
60

answered Dec 04 '19 at 11:10

user3107451

1

score -1 · Answer 7 · answered Oct 09 '18 at 14:16

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

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

7 Answers7

Linked

Related