Escaping a Quote

I needed to query a column in a table that had a single quote embedded in the string.

This is one of those little SQL tricks that can really slow you down.  I don’t know when or where I learned it but knew right away to escape the single quote with another single quote.

USE master;
GO

SELECT 'D'Agostino';

This doesn’t work because the parser sees the quoted ‘D’ but doesn’t know what to do with the trailing Agostino’ string.

SELECT 'D''Agostino'

This query works!  The first quote is the start of the string.  The second quote is the ESCAPE for the third quote.  Basically the ESCAPE makes the third quote a literal string.  Finally the fourth quote ends the string.  The parser knows what this string is and works just fine.

SELECT 'D''Agostino'''

In the above query the result set looks like: D’Agostino’.  At first glance it looks a little awkward but the 3rd from last quote is the ESCAPE for the second from last quote; the last quote simply terminates the string.
Are there other ways to do the same thing?  In SQL Server there are ALWAYS other ways to get things done and this one is no exception.
We could use the CHAR function to generate the ASCII character for the single quote.  In this case it will look like this:

SELECT 'D' + CHAR(39) + 'Agostino'

 

This is simple stuff, but only when you know it.  If you don’t know it you can spend way too much time figuring it out on your own.