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
– marc_s, Commented Jul 15, 2017 at 13:51

Kannan Kandasamy · Accepted Answer · 2017-07-15 10:39:40Z

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, 2017 at 10:39

Kannan Kandasamy

14k3 gold badges28 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Liam neesan Over a year ago

your answer is correct. But before I give correct, can you give explanation about third and 4th line?. thanks

Kannan Kandasamy Over a year ago

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

juergen d · Accepted Answer · 2017-07-15 10:42:30Z

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, 2017 at 10:42

answered Jul 15, 2017 at 10:32

juergen d

205k40 gold badges304 silver badges377 bronze badges

3 Comments

Liam neesan Over a year ago

class is an integer

Liam neesan Over a year ago

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

juergen d Over a year ago

So? Just put it in a string like in my example.

ArunGeorge · Accepted Answer · 2017-07-15 10:36:13Z

0

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/

edited Jul 15, 2017 at 10:36

answered Jul 15, 2017 at 10:31

ArunGeorge

4855 silver badges11 bronze badges

Collectives™ on Stack Overflow

How to separate string by comma using SQL Server?

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related