Surround data with single quote sql server

Question

i have variable in sql server like

DECLARE @Country varchar(max)
SET @Country='GB,US'

here country name is stored and separated by comma. i want to change and store the value in Country variable like ''GB','US','DE','FR''.

so i was trying by using stuff function like

DECLARE @Country varchar(max)
SET @Country='GB,US'
SELECT @Country=STUFF((SELECT ''', ''' + @Country
              FOR XML PATH ('')),
              1, 2, '')
print @Country

so please me to achieve it. if i could store country name like ''GB','US','DE','FR'' this way into country variable then i can issue a query like

select * from my table where country in ( @Country)

please help thanks

This question has been asked many times before, e.g. http://stackoverflow.com/questions/4974586/variable-in-expression-in-sql — Pondlife, Feb 17 '12 at 13:56
see this duplicate question: http://stackoverflow.com/questions/2773539/sqlcasting-a-string-to-ids-with-in-clause — KM., Feb 17 '12 at 13:58
possible duplicate of [Parameterizing an SQL IN clause?](http://stackoverflow.com/questions/337704/parameterizing-an-sql-in-clause) — Pondlife, Feb 17 '12 at 14:03

score 4 · Accepted Answer · edited Jul 29 '20 at 18:33

4

You can't do it this way because @country is still seen as a single variable to SQL Server, not an array (there is no such thing as an array in SQL Server).

A couple of workarounds:

1 generate dynamic SQL

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'SELECT * FROM dbo.mytable WHERE country IN (' + CHAR(39) 
    + REPLACE(@Country, ',', CHAR(39) + ',' + CHAR(39)) 
    + CHAR(39) + ');';

2 dump the comma-separated values into a split TVF (see this question for an approach, or this blog post for perf comparisons of multiple approaches).

edited Jul 29 '20 at 18:33

Community

1
1

answered Feb 17 '12 at 13:52

Aaron Bertrand

272,866
37
466
490

Because `CHAR(39)` is prettier than `''''''`. `CHAR(39)` is a single quote and when you want a string to contain one you need to escape it with an extra single quote. When you want to embed that string into another string you need to escape both again, and surround them. It gets ugly and hard to read pretty fast. – Aaron Bertrand Feb 17 '12 at 14:11

score -2 · Answer 2 · answered Feb 17 '12 at 15:01

-2

Or, if you do not want to use dynamic code:

DECLARE @my TABLE (country VARCHAR(2))
INSERT INTO @my
SELECT country FROM (VALUES ('GB'),('US'),('DE'),('FR')) t(country)

DECLARE @Country varchar(max)
SET @Country='GB,US'

SET @Country = CASE WHEN LEFT(@Country, 1) <> ',' THEN ',' ELSE '' END
                  + @Country +
               CASE WHEN RIGHT(@Country, 1) <> ',' THEN ',' ELSE '' END

SELECT * FROM @my WHERE CHARINDEX(',' + country + ',', @Country) > 0

answered Feb 17 '12 at 15:01

Kirdahaa

1

Assume the string `'GB,US'` comes in as a parameter. Can you suggest how to construct your `VALUES` clause (even if the user is on SQL Server 2008 or better, since that syntax won't work on earlier versions)? – Aaron Bertrand Feb 17 '12 at 15:11
the way u show to solve this trick is very bad...in my case 'GB,US' will come as param. – Thomas Feb 20 '12 at 06:32

Surround data with single quote sql server

2 Answers2