Create string with embedded quotes in SQL

Question

I run several queries that use a list of character values in the where clause, e.g.,

select  *
from    table1
where   col1 in ('a','b','c')

The character list changes frequently, so I want to store the string in a variable and reference the variable in all of the queries instead of maintaining several copies of the string. I've tried the following but the query returns zero rows.

declare @str varchar(50)
select @str = '''a''' + ',' + '''b'''+ ',' + '''c'''
select  *
from    table1
where   col1 in (@str)

@str has the value 'a','b','c' but for some reason, SQL Server doesn't recognize it. How do I build a string and store it in a variable that works with the in keyword?

what this does / result is 'a','b','c' being one and only one varchar, not 3 — Fredou, Mar 21 '15 at 00:21
possible duplicate of [Parameterize a SQL IN clause](http://stackoverflow.com/questions/337704/parameterize-a-sql-in-clause) — Dai, Mar 21 '15 at 00:37

WorkSmarter · Answer 1 · 2015-03-21T01:26:12.720

0

It is possible to create a string with embedded quotes. As Fredou and ChrisS mentioned, @str is considered a single string. If the @str value is concatenated with the rest of your select statement and then executed, you will achieve the your desired results. SQL Fiddle example.

declare @str varchar(50)
declare @sql varchar(MAX)

select @str = '''a''' + ',' + '''b'''+ ',' + '''c'''    

Select @sql = 'SELECT * FROM table1 WHERE col1 IN (' + @str + ')'

Exec(@sql)

Results using @str = '''a''' + ',' + '''b'''+ ',' + '''c'''

enter image description here

Results using @str = '''a''' + ',' + '''b'''

enter image description here

edited Mar 21 '15 at 01:26

answered Mar 21 '15 at 00:18

WorkSmarter

3,738
3
29
34

Hi @JimG if this or any answer has solved your question please consider [accepting it](http://meta.stackexchange.com/q/5234/179419) by clicking the check-mark. This indicates to the wider community that you've found a solution and gives some reputation to both the answerer and yourself. There is no obligation to do this. – WorkSmarter Mar 21 '15 at 01:19

score 0 · Accepted Answer · answered Mar 21 '15 at 00:51

The IN construct in SQL as a set lookup, not a string lookup. Your single string value of "'a','b','c'" is exactly what it's looking for when you say where col1 in (@str)... as Fredou mentioned in comments.

Instead you want to pass in a set of values by using a table variable (or a temp table):

declare @tabIn table ( val varchar(10) )
insert @tabIn
    (val) values
    ('a'), ('b'), ('c')

select *
    from table1
    where
        col1 in (select val from @tabIn)

or, alternatively, just do a straight join:

declare @tabIn table ( val varchar(10) )
    insert @tabIn
        (val) values
        ('a'), ('b'), ('c')

select *
    from table1 t1
    join @tabIn t2 on
        t1.col1 = t2.val

Create string with embedded quotes in SQL

2 Answers2