CODEWITHSUNDEEP
sqlsql-servert-sqlescaping
user4461417
user4461417

Reputation:

Single quote represented by double single quotes not working

I have the following sql query

set @st = @st + ' locationcity=''St. John''s'' order by datet desc'; execute(@st);

I used double single quotes because that the way to use single quote in query.

But I am getting following error

Incorrect syntax near 's'.

Unclosed quotation mark after the character string ' order by datet desc'.

How can I resolve this error?

Upvotes: 0

Views: 1383

Answers (3)

A_Sk
A_Sk

Reputation: 4630

Try this.

 set @st = @set+' locationcity=''St. John''''s'' order by datet desc'
 execute(@st);

see this example.

 create table #tab (name varchar(50))
 insert into #tab values('hello1')
 declare @set as varchar(max)
 set @set='update #tab '
 set @set=@set+'set name=''john''''s''' --updating name with john's
  exec(@set)
 select * from #tab

Upvotes: 0

Codeek
Codeek

Reputation: 1624

your resultant query is 

 locationcity='St. John's' order by datet desc

you can see that second last ' before s is considered an closing quote, and hence gives you an error and the next ' act as opening quote without a closing quote and hence one more error. try 

''St. John''''s''

Upvotes: 1

Dgan
Dgan

Reputation: 10295

try this:

Use Print for debugging

Declare @st varchar(1000)='';
set @st=@st+' locationcity=''St. John''''s'' order by datet desc';
print @st
 execute(@st);

Upvotes: 1

Related Questions