Menu
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.
eg. insert into my_table values('hi, my name''s tim.');
522
No escaping is used with single quotes. Use a double backslash as the escape character for backslash.
You need to escape a single quote when the literal is enclosed in a single code using the backslash(\) or need to escape double quotes when the literal is enclosed in a double code using a backslash(\).
We can use the following ASCII codes in SQL Server: Char(10) – New Line / Line Break. Char(13) – Carriage Return. Char(9) – Tab.
The apostrophe, or single quote, is a special character in SQL that specifies the beginning and end of string data. This means that to use it as part of your literal string data you need to escape the special character. With a single quote this is typically accomplished by doubling your quote.
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. If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
