Single quotes are escaped by doubling them up, just as you’ve shown us in your example. The following SQL illustrates this functionality. I tested it on SQL Server 2008:
DECLARE @my_table TABLE ( [value] VARCHAR(200) ) INSERT INTO @my_table VALUES ('hi, my name''s tim.') SELECT * FROM @my_table
value ================== hi, my name's tim.
How do I escape a single quote in SQL Server?
If escaping your single quote with another single quote isn’t working for you (like it didn’t for one of my recent
REPLACE() queries), you can use
SET QUOTED_IDENTIFIER OFF before your query, then
SET QUOTED_IDENTIFIER ON after your query.
SET QUOTED_IDENTIFIER OFF; UPDATE TABLE SET NAME = REPLACE(NAME, "'S", "S"); SET QUOTED_IDENTIFIER ON; -- set OFF then ON again
insert into my_table values('hi, my name' + char(39) + 's tim.')
Many of us know that the Popular Method of Escaping Single Quotes is by Doubling them up easily like below.
PRINT 'It''s me, Arul.';
we are going to look on some other alternate ways of escaping the single quotes.
1. UNICODE Characters
39 is the UNICODE character of Single Quote. So we can use it like below.
PRINT 'Hi,it'+CHAR(39)+'s Arul.'; PRINT 'Helo,it'+NCHAR(39)+'s Arul.';
Another simple and best alternate solution is to use QUOTED_IDENTIFIER. When QUOTED_IDENTIFIER is set to OFF, the strings can be enclosed in double quotes. In this scenario, we don’t need to escape single quotes. So,this way would be very helpful while using lot of string values with single quotes. It will be very much helpful while using so many lines of INSERT/UPDATE scripts where column values having single quotes.
SET QUOTED_IDENTIFIER OFF; PRINT "It's Arul." SET QUOTED_IDENTIFIER ON;
The above mentioned methods are applicable to both AZURE and On Premises .
The doubling up of the quote should have worked, so it’s peculiar that it didn’t work for you; however, an alternative is using double quote characters, instead of single ones, around the string. I.e.,
insert into my_table values("hi, my name's tim.");
2 ways to work around this:
' you can simply double it in the string, e.g.
select 'I''m happpy' — will get:
For any charactor you are not sure of: in sql server you can get any char’s unicode by
select unicode(':') (you keep the number)
So this case you can also
select 'I'+nchar(39)+'m happpy'
Also another thing to be careful of is whether or not it is really stored as a classic ASCII ‘ (ASCII 27) or Unicode 2019 (which looks similar, but not the same).
This isn’t a big deal on inserts, but it can mean the world on selects and updates.
If it’s the unicode value then escaping the ‘ in a WHERE clause (e.g where blah = ‘Workers”s Comp’) will return like the value you are searching for isn’t there if the ‘ in “Worker’s Comp” is actually the unicode value.
If your client application supports free-key, as well as copy and paste based input, it could be Unicode in some rows, and ASCII in others!
A simple way to confirm this is by doing some kind of open ended query that will bring back the value you are searching for, and then copy and paste that into notepad++ or some other unicode supporting editor.
The differing appearance between the ascii value and the unicode one should be obvious to the eyes, but if you lean towards the anal, it will show up as 27 (ascii) or 92 (unicode) in a hex editor.
I am trying to
insert some text data into a table in SQL Server 9.
The text includes a single quote
How do I escape that?
I tried using two single quotes, but it threw me some errors.
insert into my_table values('hi, my name''s tim.');
Hope you learned something from this post.
Follow Programming Articles for more!