Escape single quotes and wild cards ‘%’ , ‘_’ in MS SQL

Posted: December 24th, 2007 | Author: Nischal Shetty | Filed under: SQL | Tags: escape ', escape single quote, Ms Sql escape character | 10 Comments »

To escape single quotes ( ‘ ) in MS SQL all you need to do is add two single quotes instead of one.

Ex. If you want to insert the name D’souza into MS SQL the query goes like this:

INSERT into name_column ([name]) VALUES (‘D”Souza’)

Look carefully. We have replaced single quote in between the letter D and the word SOUZA with two SINGLE QUOTES. (It’s not a double quote)

Similarly, you may also face a problem while querying for values which contain the characters ‘%’ and ‘_’ in them. This is so because SQL considers these two characters as wild cards for multiple characters and single character string matching.

There are 2 ways to escape these wild cards.

1. You can use the default square braces “[]” like this- [%] or [_]

2. You can define your own escape character by using the keyword ESCAPE at the end of your where clause.

Ex. select * from name_column where name like ‘gyan\_sagar’ ESCAPE ‘\’

In the example above we escape “_” by defining “\” as an escape character using the ESCAPE keyword.

Note: You can only define a single character for escaping and not a string.

I hope this post has made your work a bit more easier than before. Your suggestions and comments are always welcome.

http://www.techtamasha.com/wp-content/plugins/sociofluid/images/digg_48.png

http://www.techtamasha.com/wp-content/plugins/sociofluid/images/reddit_48.png

http://www.techtamasha.com/wp-content/plugins/sociofluid/images/stumbleupon_48.png

http://www.techtamasha.com/wp-content/plugins/sociofluid/images/delicious_48.png

http://www.techtamasha.com/wp-content/plugins/sociofluid/images/furl_48.png

http://www.techtamasha.com/wp-content/plugins/sociofluid/images/technorati_48.png

http://www.techtamasha.com/wp-content/plugins/sociofluid/images/google_48.png

http://www.techtamasha.com/wp-content/plugins/sociofluid/images/myspace_48.png

http://www.techtamasha.com/wp-content/plugins/sociofluid/images/facebook_48.png

http://www.techtamasha.com/wp-content/plugins/sociofluid/images/yahoobuzz_48.png

http://www.techtamasha.com/wp-content/plugins/sociofluid/images/twitter_48.png

10 Comments on “Escape single quotes and wild cards ‘%’ , ‘_’ in MS SQL”

1 Thanks said at 3:09 pm on March 24th, 2008:
It really helped me.
2 Nischal Shetty said at 4:40 am on March 26th, 2008:
Hey… I’m glad it did
3 James Socol said at 6:41 pm on June 2nd, 2008:
Thank you! You have no idea how much trouble you saved me.

Who at MS thought this was a good idea? “The rest of the world uses a slash, so we should definitely do something dumb, instead.”
4 Nischal Shetty said at 1:30 am on June 3rd, 2008:
Hey thanks a lot for the encouraging comment on your part James. Motivates me to blog more
5 Alex said at 5:21 pm on July 14th, 2008:
Thx very much
6 Nischal Shetty said at 1:10 am on July 15th, 2008:
My pleasure Alex
7 kam99 said at 3:20 pm on October 7th, 2008:
Just for completeness =)

Have you ever think you’ll need to search-like for square brace itself? Some ‘-![CoolName]!-’ for example?

The solution is a bit tricky – you need to escape only opening [ square.
select * from table where column like '%[[]%’

other workable expressions – like ‘%]%’, ‘%[[]%]%’

Not very obvious, but still logical

(way 2 is possible here too, but how many ORMs support such syntax?)
8 Nischal Shetty said at 3:41 pm on October 7th, 2008:
@Kam99

Thanks for sharing this info here. Thats a bit tricky alright

About the second way of using ESCAPE.. I’m not too sure if its supported in other databases.
9 Ashok said at 6:34 am on August 4th, 2009:
Thank’s yaar,
it increses few more drop in my gyan sagar….
10 Nischal Shetty said at 3:46 pm on August 4th, 2009:
@Ashok

you’re welcome buddy