Feeds:
Posts
Comments

Posts Tagged ‘SQL SERVER – How to insert a string value with an apostrophe (single quote) in a column’

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

singlequotes1.1

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.

Advertisements

Read Full Post »