bobox00
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 ')
'.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Did you use two ' when you tried my suggestion? I tried out the same code and it was able to return the record.
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.
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.
Doubling the quote will work
You can also specify a single character using and under score
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%')
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
i have tried this on my temp database and its working fine.
select * from temp1 where filename like '%dfg''dfgdfg%'
select * from temp1 where filename like '%dfg''dfgdfg%'
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.
ASKER
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"