How to insert a string value with an apostrophe (single quote) in a column is a general problem? Mostly, it happens when you insert any name with apostrophe. One of my colleagues faced this issue this morning. He had to upload a list of customers and some of the customers had apostrophe in the name and he was getting error during insertion. After a while, he emailed me stating he fixed it. So I inquired him how he did it and I received a very interesting reply, he said he copied the name then opened the table and pasted it ;). Opsssssssss. Never try this method.
Lets now resolve it step by step.
Step 1 :
Create a sample table.
USE tempdb GO CREATE TABLE tbl_sample ( [ID] INT, [Name] VARCHAR(50) ) GO
Step 2 :
Insert the name with apostrophe. This step is just to demonstrate the error.
USE tempdb GO INSERT INTO tbl_sample VALUES (1,'Irwin D'Mello') GO --OUTPUT
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ‘Mello’.
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string ‘)
‘.
Ooopps…… I am unable to insert it.
Step 3 :
Just replace the single apostrophe with double apostrophe and insert the record again.
USE tempdb GO INSERT INTO tbl_sample VALUES (1,'Irwin D''Mello') GO --OUTPUT
(1 row(s) affected)
Step 4 :
Lets check if the data is inserted or not.
USE tempdb GO SELECT * FROM tbl_sample GO --OUTPUT
You can now see the name in the right format.
Conclusion :
Remember, whenever you come across such cases, just replace apostrophe (single quote) with double apostrophe (double quotes) and it works fine.
Thanks for sharing this, you always simplify things for us 🙂
Thanks. It helped!
Thank you for sharing this.
Thank you for sharing this.