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