I'm trying to insert some text data into a table in SQLServer 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.');
+1
A:
How about:
insert into my_table values('hi, my name'+chr(39)+'s tim.')
Paul McGuire
2009-10-19 01:24:47
+7
A:
Are you sure? This worked perfectly fine with SQL Server 2008.
CREATE TABLE my_table (
value VARCHAR(200)
)
GO
INSERT INTO my_table VALUES ('hi, my name''s tim.')
SELECT * FROM my_table
Which are you using? Did you have any other statements besides the INSERT?
Cory Larson
2009-10-19 01:30:51
i was looking at the wrong place to fix my problem. it was not a character escape issue after all. my issue was that the data length was over the limit. thanks for reassuring me that using the single quote twice is the right way of escaping the character.
tim_wonil
2009-10-19 01:41:39
+1
A:
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.");
Alex Martelli
2009-10-19 01:33:44
A:
I'm agree with Cory Larson, when I'm using MSSQL 2005 I use the single quote to escape the single quote. Sounds weird but it's work for me. Try with that.
Edit: Du'h, I forget the reference: MSSQL 2005 escaping quotes