Escape for SQL Query

I use Microsoft SQL Server 2008 and got an error message for the query below:

select * from conthist
WHERE  (Conthist.REF LIKE '%Are you ready for John's Next Single%')

The error message is:
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 's'.
Msg 105, Level 15, State 1, Line 2
Unclosed quotation mark after the character string ')
'.

bobox00Asked:
Who is Participating?
 
tim_csCommented:
Try this. Escape with double single quotes.


WHERE  (Conthist.REF LIKE '%Are you ready for John''s Next Single%')

0
 
bobox00Author Commented:
Tried that, I get no errors, but zero rows. However, if I query with

WHERE  (Conthist.REF LIKE '%Are you ready for John%s Next Single%')

I get 163 rows with the REF column containing "Are you ready for John's Next Single"
0
 
tim_csCommented:
Did you use two ' when you tried my suggestion?  I tried out the same code and it was able to return the record.  
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
bobox00Author Commented:
Used two ' and it's definitely returning zero rows for me. Not sure why it seems to work for everybody else.
0
 
BrandonGalderisiCommented:
Perhaps your data contains an apostrophe and not a single quote?

Use the function in this article and it will split every character out into a string.  There is sample SQL to give you the ASCII values.  single quote is 39, apostrophe is 96.
0
 
Ephraim WangoyaCommented:
Doubling the quote will work
You can also specify a single character using and under score
select * from conthist
WHERE  (Conthist.REF LIKE '%Are you ready for John_s Next Single%')

Open in new window

0
 
BrandonGalderisiCommented:
0
 
keyuCommented:
i have tried this on my temp database and its working fine.

select * from temp1 where filename like '%dfg''dfgdfg%'
0
 
bobox00Author Commented:
Thanks guys! I was trying to get this right for use in an update statement that has a replace function in it. I was able to achieve my purpose by removing the replace function and just using a straight update statement. Hence, I was able to avoid the single quote problem.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.