Function in SQL Server 2008 to put single quotes around each element of a list

Question

I have a stored procedure that is receiving as a parameter a list of values to include in a query. For example NY, NJ, CT, PA

In my dynamic SQL query in the stored procedure, I want to select records

WHERE state IN ('NY', 'NJ', 'CT', 'PA').

If I use the parameter as it gets to the SP, I would be doing

WHERE state IN 'NY, NJ, CT, PA'.

Therefore, I need to convert

'NY, NJ, CT, PA'

to

('NY','NJ','CT','PA').

Is there a function to do that?

Thanks!

Hey, I think Kevin pointed in the right direction here. All I need to do is:

WHERE state IN REPLACE(('NY, NJ, CT, PA'), ',' , ''',''')

Or, since I really have it in a variable...

WHERE state IN REPLACE ((@StateList), ',' , ''',''')

http://www.codeproject.com/Articles/5986/Passing-a-Comma-Delimited-Parameter-to-a-Stored-Pr — JonH, May 17 '12 at 14:29
You say its a dynamic query? so is `WHERE state in ..` etc in a string already? — Alex K., May 17 '12 at 14:31
Do you have any control of how the data is passed to the stored proc? — bendataclear, May 17 '12 at 14:31
If you have flexibility to change your stored procedure, try `where patindex('%'+state+'%', 'NY, NJ, CT, PA') > 0` instead of `WHERE state IN ('NY', 'NJ', 'CT', 'PA')`. Otherwise, use a user-defined function to build a list from your string. — Sergey Kalinichenko, May 17 '12 at 14:40
possible duplicate of [Parameterizing an SQL IN clause?](http://stackoverflow.com/questions/337704/parameterizing-an-sql-in-clause) — Pondlife, May 17 '12 at 16:56

score 1 · Answer 1 · edited May 23 '17 at 12:31

1

There are many ways to do this. One simple way would be to insert them to a temp table (e.g. @temp_states) and do this:

WHERE state in (SELECT state from @temp_states)

Instead of inserting them to a table, you can use a function like this one to generate the table for you:

WHERE state in (SELECT * from dbo.Split(',', 'NY,NJ,CT,PA'))

edited May 23 '17 at 12:31

Community

1
1

answered May 17 '12 at 14:30

PinnyM

35,165
3
73
81

bendataclear · Answer 2 · 2012-05-17T14:43:04.617

1

A very simple option is to do:

WHERE CHARINDEX ( CONCAT (', ', state,',') ,  CONCAT (', ', @PARAM,',')) > 0

edited May 17 '12 at 14:43

answered May 17 '12 at 14:37

bendataclear

3,802
3
32
51

2

You might want to modify that. As-is, it would have issues with the spaces in between the states. – Kevin Fairchild May 17 '12 at 14:41
2

In addition to what Kevin said, there will be issues with the first and the last entries on the list. – Sergey Kalinichenko May 17 '12 at 14:42
1

Thanks, didn't spot the spaces. – bendataclear May 17 '12 at 14:42

score 1 · Accepted Answer · answered May 17 '12 at 14:40

1

Here's a fun way to do it without a function:

DECLARE @StateList VARCHAR(50)='NY, NJ, CT, PA' --(substitute for your parameter)
SELECT * FROM Table WHERE ',' + REPLACE(@StateList,' ','') + ',' LIKE '%,' + State + ',%'

answered May 17 '12 at 14:40

Kevin Fairchild

10,891
6
33
52

Hey Kevin, thanks a lot. You gave me the idea. I think all I have to do is REPLACE ',' for ''','''. – Amarundo May 17 '12 at 15:26
Oh! I'm sorry, @Amarundo, I guess I missed that this was a Dynamic SQL query you were doing. Glad my suggestion still helped, though. – Kevin Fairchild May 17 '12 at 17:21

Function in SQL Server 2008 to put single quotes around each element of a list

3 Answers3