Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 909
  • Last Modified:

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

0
bobox00
Asked:
bobox00
  • 3
  • 2
  • 2
  • +2
2 Solutions
 
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now