Today, when I was trying to insert a value to a table in SQL Server which had single quotes, I ended by getting the following error.
Unclosed quotation mark after the character string
The query looked like this
INSERT INTO COUNTRIES (NAME) VALUES (‘Test’s’)
How to escape a single quote in SQL Server?
To fix the above error, we can escape the single quotes by doubling or inserting two single quotes as shown in the below query
INSERT INTO COUNTRIES (NAME) VALUES (‘Test”s’)
1 Comment
A better way would be to use the ESCAPE ‘\’ syntax, which then allows you to put a ‘\’ character in front of the intermediate ‘ to ‘escape’ this.
If you’re creating dynamic SQL then this is an obvious security issue, and you should be dynamically escaping the string prior to substitution, or using an alternative way of passing parameters into the SQL command (like parameterised queries)
Bevan