SQL

SQL — 2 Comments
17
Jan 08

Wildcards(“_” and “%”) in SQL

We can use the WILDCARDS “_” and “%” in our SQL queries be it MS SQL or My SQl as follows:

Wildcard underscore “_” :

“_” helps to match exactly one character.

Ex. select name from name_table where name like “c_p”

The above query will return words such as “cap”, “cop”, “cup” or basically anything which is a three letter word starting with the letter “c” and ending with “p”.

Wildcard “%” :

“%” helps match any number of characters. Let’s take the same example as above:

select name from name_table where name like “c%p”

The output can be “cap”, “cop” and can also be “coooop”, “caawjshygfvsbchjsp” etc etc.

So what basically happens with “%” is that it will include any number of characters whereas “_” will include only one character.

SQL — 12 Comments
24
Dec 07

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

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. :)