CODEWITHSUNDEEP
sql-server
Saksham
Saksham

Reputation: 9390

Escaping single quote in SQL Server

I was trying to execute the below statement to escape single quotes (i.e. using two single quotes):

declare @year varchar(max)
set @year = '111,11';
exec ('SELECT * FROM SplitValues(' + @year + ','','')');

I even tried to use char(39) instead of quotes:

declare @year varchar(max)
set @year = '111,11';
exec ('SELECT * FROM SplitValues(' + @year + ',' + char(39) + ',' + char(39) + ')');

But it didn't help. These are the only two solutions that I found on this site. Any help?

This is the simplified query to clear up all your questions:

declare @year varchar(max)
set @year = '111,11';
SELECT * FROM SplitValues(@year , ',')

I want to achieve this, but using a dynamic query.

Upvotes: 22

Views: 96190

Answers (4)

Anil Kale
Anil Kale

Reputation: 1

declare @var1 varchar(100)
declare @var3 varchar(100)
declare @var4 varchar(100)

declare @var2 nvarchar(MAX)
set @var1 = ‘anil’
set @var4 = ‘1019518594’

set @var2 = N’select
a.*
from card b
join log a on a.Cust = b.ID
where a.c = ”’ + @var1 + ”’ and b.s =”’+ @var4 +””

print(@var2)

exec sp_executesql @var2

Upvotes: -1

PilotBob
PilotBob

Reputation: 3117

Ok... you want to take this string:

SELECT * FROM SplitValues(@year , ',')

And make it a string like this:

'SELECT * FROM SplitValues('111,11' , '','')'

So, your final code would be:

declare @year varchar(max), @sql varchar(max)
set @year = '111,11';
set @sql = 'SELECT * FROM SplitValues(''' + @year + ''' , '''','''')'

select @sql

Actually, finally select you would use exec() instead. However you really should probably use sp_sqlexecute for stuff like this since you can use paramaterized queries.

Upvotes: 3

Andriy M
Andriy M

Reputation: 77717

A word of advice. When testing a dynamic script, first just display it instead of executing it. That way you will be able to see it exactly as it would be seen by the EXEC statement.

Now to the issue. You should keep in mind that you are not passing the variable to SplitValues but are instead concatenating the variable's value into the script. Since the value is varchar, it should be concatenated with quotation marks around it. The absence of them is the only problem really.

The quotes around the second argument, the comma, are escaped correctly in both cases. So, just use either of the methods to add the quotes around the first argument:

  • repetition of the quotation mark:

    DECLARE @year varchar(max), @sql varchar(max);
SET @year = '111,11';
SET @sql = 'SELECT * FROM SplitValues(''' + @year + ''','','')';
SELECT @sql;

  • using CHAR(39):

    DECLARE @year varchar(max), @sql varchar(max);
SET @year = '111,11';
SET @sql = 'SELECT * FROM SplitValues(' + CHAR(39) + @year + CHAR(39) + ',' + CHAR(39) + ',' + CHAR(39) + ')';
SELECT @sql;

Obviously, the first method is more compact, but, like I said, both work well, as this SQL Fiddle demo clearly shows.

Note, however, that you could easily escape this issue in the first place, if you pardon the pun. Instead of EXEC (), you could use EXEC sp_executesql, which allows you to use parameters. Here's the same script rewritten to use sp_executesql:

DECLARE @year varchar(max), @delim char(1);
SET @year = '111,11';
SET @delim = ',';
EXEC sp_executesql
  N'SELECT * FROM SplitValues(@year_param,@delim_param)',
  N'@year_param varchar(max), @delim_param char(1)',
  @year,@delim;

As you can see, no need to worry about escaping the quotes: SQL Server takes the trouble of substituting the values correctly, not you.

Upvotes: 40

AdamL
AdamL

Reputation: 13191

Just type single quote two times:

select 'that''s it'

Upvotes: 16

Related Questions