Embedded Apostrophe in a Query

Question:
When I pass a string containing an embedded apostrophe in a query to SQL Server, it sees the apostrophe as an end-of-string marker.

Example:

"Select * from Members Where LastName = 'O'Brien'"

What’s the solution to this scenario?

Answer:
SQL Server allows the use of either the apostrophe or the double-quote to delimit a string.

The easiest way to solve your problem is to use the double-quote character as your delimiter. This way, everything between the two quotes, including the apostrophe, is treated as part of the string.

Example:

Select * from authors Where au_lname = "O'Brien'"

works just fine.

Similarly, you could use the apostrophe to delimit the string when a quotation mark is in the string. So

"Thank you," he said

can be written as

Select '"Thank you," he said'

However, if you have both in the string, as in

"Don't do it," he said

you then have to resort to using a double delimiter. Whenever you either use ” ” or ‘ ‘, the system translates it as a signal to use the apostrophe or the quote as part of the string instead of a delimiter.

So

"Don't do it," he said

becomes

Select """Don't do it,"" he said"

Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

Recent Articles: