How to escape single quote in SQL Server?

by Ginktage | Saturday, Dec 22, 2012 | 190 views

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’)

If you enjoyed this post, please consider leaving a comment or subscribing to the RSS feed to have future articles delivered to your feed reader.
Like it? Share it!

1 Comment so far. Feel free to join this conversation.

  1. Bevan Weiss August 8, 2013 at 5:22 am - Reply

    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

Leave A Response