How to separate string by comma using SQL Server?

Question

I wants to separate string by comma. Actually I wants to filter in WHERE Clause like

CREATE PROC spGetRecords
@class varchar(50)
AS
BEGIN
    SELECT *
    FROM SampleTable
    WHERE class in (@class)  --in Database class is an integer
END

So, I want to pass the parameter when execute query like below

spGetRecords @class = '12,22,45,66'

I think it's not possible to pass multiple value in single parameter in my case.

So, If I remove separate the string by ',' then I can run my query in while loop that will bring the record correct?

So, How can I separate the string by comma

If you're using SQL Server **2008** or newer, you should use **table-valued parameter** which is the *proper* and most efficient way of doing this — marc_s, Jul 15 '17 at 13:51

score 3 · Accepted Answer · answered Jul 15 '17 at 10:39

3

You can try splitting using xml path as below:

Declare @delimiter nvarchar(max) = ','
Declare @str nvarchar(max) = '12,22,45,66'

Declare @xml as xml
        Select @xml = CAST('<x>' + REPLACE((SELECT REPLACE(@str,@delimiter,'$$$SSText$$$') AS [*] FOR XML PATH('')),'$$$SSText$$$','</x><x>')+ '</x>' AS XML) 

--select @xml
Select y.value(N'text()[1]', N'nvarchar(MAX)') as value 
FROM @xml.nodes(N'x') as x(y)

If you are in SQL Server >= 2016 you can use STRING_SPLIT() as below:

Select * from string_split('12,22,45,66',',')

answered Jul 15 '17 at 10:39

Kannan Kandasamy

13,405
3
25
38

your answer is correct. But before I give correct, can you give explanation about third and 4th line?. thanks – Liam neesan Jul 15 '17 at 10:50
Try selecting individual lines you will understand, first step converting into xml using xml path, while create nodes i am changing comma/delimiter as separate node and second line traversing thru all nodes – Kannan Kandasamy Jul 15 '17 at 10:52

juergen d · Answer 2 · 2017-07-15T10:42:30.413

1

Use dynamic SQL

exec 'SELECT * FROM SampleTable WHERE class in (' + @class + ')'

which will patch the strings together and then execute it like this:

SELECT * FROM SampleTable WHERE class in (12,22,45,66)

edited Jul 15 '17 at 10:42

answered Jul 15 '17 at 10:32

juergen d

201,996
37
293
362

class is an integer – Liam neesan Jul 15 '17 at 10:38
it's a multilne query, not single line. For giving an example only I mentioned sample table.. But actually it is little bit lengthy query – Liam neesan Jul 15 '17 at 10:42
So? Just put it in a string like in my example. – juergen d Jul 15 '17 at 10:43

ArunGeorge · Answer 3 · 2017-07-15T10:36:13.030

Here is a good solution - Converting String List into Int List in SQL . Idea here is to send in an int list to a stored procedure as an XML parameter.

Here is another quick but dirty solution - t-sql Convert comma separated string into int, without using user created function . Here the passed in value is used within a dynamic sql statement.

More ideas - http://sql-articles.com/scripts/function-to-split-comma-separated-string-to-integer/

How to separate string by comma using SQL Server?

3 Answers3

Linked