How to use (escape) single quotation mark in sql statements


We all get that day when we get an exception complaining about the single quotation or apostrophe that was in our SQL statement.

The best advisable thing to do is to use stored procedures so that all data are passed as parameters. However there are situations we have to use in-line SQL statements and even there are situations where even SQL parameters cannot manage this issue.

For example, if you use the exec method in you stored procedure body to do some dynamic stuff [read more about using exec to generate dynamic queries in stored procedures here ] you will have noticed that even if you pass a string with an apostrophe to an sql parameter it will still throw an exception at you !

So the only way out is to escape this character. Once you instruct the SQL parser to escape the character it will take the apostrophe as part of the string input not part of the command.

How to?

Simply replace your apostrophe / single quotation with two apostrophes / two single quotations .

Replace Bob’s world With Bob’s world  <- these are two single quotation characters (not one double quotation character)

This can be easily done by using the string.replace method.

Was this post helpful to you? How can I improve? – Your comment is highly appreciated!

Cassian Menol Razeek

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: