SQL: wildcards

Tweet

While we all know about the "%" wildcard, we might not know that there are other interesting ways of using special characters when building queries.
I suppose you are aware of what a wildcard is. Anyway, if you're new to SQL, wildcards are special characters used in queries and they can be used to substitute one or more characters while retrieving data from a database.
We must remember that we can use wildcards only with the "LIKE" operator.

Before going into the available wildcards, let me show you a basic example:

SELECT productCode, description, price
FROM prodTable
WHERE description LIKE 'p%'

The above query will retrieve all the products that have a description starting with "p" from the prodTable table.
Now, let's see which are the available wildcards.

The wildcards

Basically we have 4 types of wildcards.
1) % - it will substitute zero or more characters;
2) _ - it will substitute precisely one character;
3) [chars] - it will search any single character in chars;
4) [!chars] - it will search any single character NOT in chars.

Ok. I really would like to know how many of you knew about at least the last two options.
Anyway, let's get on and see an example for each wildcard.

"%" wildcard

As seen above, the "%" will substitute zero or more characters. We can use it alone (not very useful uh?) or together with other characters. We can even use it twice.
So, possible use of this wildcard (after the LIKE operator):

LIKE '%' - any result
LIKE 'p%' - starting with "p"
LIKE '%p%' - with "p" somewhere (it doesn't matter if at beginning, in between or at the end)
LIKE '%a' - ending with a

"_" wildcard

The "_" wildcard substitutes exactly one character. It works like the "%" wildcard, but it will consider a number of characters identical to the number of "_".
Some example:

LIKE '_' - results with just one character, any character
LIKE '__' - results with two characters, any characters in any combination
LIKE '_a' - results with any character followed by an "a"
LIKE '_a_' - results with any character followed by an "a" and any character again
LIKE 'a_' - results starting with "a" and followed by any character (one character)
LIKE '_____a' - results starting with 5 characters in any combination and ending with "a"

Isn't it getting interesting?
We can use the "%" and "_" wildcards together, in order to create powerful filters in queries. I leave it to your imagination, however I believe it is not that difficult to foresee the use of wildcards combinations.

[chars] and [!chars] wildcard

This is a little bit trickier. We normally use this wildcard in combination with "%". For example:

LIKE '[abc]%' - results starting with "a", "b" or "c"

The same we can do with the [!chars] wildcard:

LIKE '[!abc]%' - results NOT starting with "a", "b" or "c"

Again, the combination of different wildcards can be very effective in complex queries.
But...

Wildcards usage problem

There's a problem. Wildcards are surely very effective and powerful but they are not very efficient. What I mean is that they slow down the server response. The more complex they are, the slower will be the query.
It's quite clear that if the server needs to retrieve specific data based on a complex LIKE operator, it has to - virtually -  go through every record and see if it meets the required characteristics. It's a lot of job to do, and that ends up in increasing the response time.
So, be careful when using the LIKE operator in general, and specifically wildcards.

See you next time, in the meantime, happy coding!