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

By on April 6, 2022

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. .

Lets now resolve it step by step.

Step 1 :
Create a sample table.

12345678USE tempdbGOCREATE TABLE tbl_sample(  [ID] INT,  [Name] VARCHAR(50))GO

Step 2 :
Insert the name with apostrophe. This step is just to demonstrate the error.

12345USE tempdbGOINSERT 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.

12345USE tempdbGOINSERT 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.

12345USE tempdbGOSELECT * FROM tbl_sampleGO--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.

About Thea

You must be logged in to post a comment Login