Escaping single-quotes in SQL

No, I don’t mean escaping as in escaping from a prison. (Har-de-ha-ha-har, that was not very funny now was it?).

The question I am really asking is, if you wanted to represent a string literal that has a single quote in it, for example McDonald’s how would you do it?

-- this does not work
select 'McDonald's' as RestauarantName;

Of course, this isn’t rocket science or brain surgery; you know the answer. Escape the single quote with another single quote.

-- this DOES work!
select 'McDonald''s' as RestauarantName;

PS: I am wondering whether I can nominate this as the shallowest of all the blog articles that I have written. What do you think? Do a “-1” or “DISLIKE” or something like that for this blog if you think it qualifies.

By the way, the rule “escape a single quote with another escape” works even when you want to nest string literals. What I mean can be demonstrated by an example:

-- This piece of crap does not work because of single quotes problem.
DECLARE @sql NVARCHAR(4000);
SET @sql = 'select 'McDonald''s' as RestaurantName';
EXEC (@sql);

To make that work, apply the “escape single quote with another single quote rule” one more time

-- this crap DOES work.
DECLARE @sql NVARCHAR(4000);
SET @sql = 'select ''McDonald''''s'' as RestaurantName';
EXEC (@sql);

So now you know everything that is there to know about escaping single quotes. Well, may be not everything, but my goal is mediocrity, so as they say in goverment offices, “this is good enough for goverment work!”

Until I can think of something shallower or crappier to post, AU REVOIR!!!

Advertisements

One thought on “Escaping single-quotes in SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s