Link to home
Start Free TrialLog in
Avatar of bobox00
bobox00Flag for United States of America

asked on

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 ')
'.

ASKER CERTIFIED SOLUTION
Avatar of tim_cs
tim_cs
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of bobox00

ASKER

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"
Did you use two ' when you tried my suggestion?  I tried out the same code and it was able to return the record.  
Avatar of bobox00

ASKER

Used two ' and it's definitely returning zero rows for me. Not sure why it seems to work for everybody else.
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.
Avatar of Ephraim Wangoya
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

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
i have tried this on my temp database and its working fine.

select * from temp1 where filename like '%dfg''dfgdfg%'
Avatar of bobox00

ASKER

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.