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"
