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!!!