devxlogo

Embedded Apostrophe in a Query

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"

devxblackblue

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist