The SQL LIKE clause is a very handy little tool, and it’s a very simple one to use.
A LIKE clause is a wildcard search placed in the WHERE clause, generally used to find a range of data by searching for a character string within a column.
There are several different searchs you can run with a LIKE, and I’ll explain each of them below.
The most common is probably the percent (%) symbol. The % replaces zero or more characters in a string, then searches for matchs of your string.
WHERE FirstName LIKE '%ach'
would return ‘Zach’ and anything else that ends in “ach”. The % can be used on either side of the string, or on both sides. You also don’t actually need to specify a string (LIKE ‘%%’), but doing this will return absolutely everything. I can’t see a use for this myself, but it’s there if you need it!
The next wildcard search is the underscore (_). This replaces one character in a character string. For instance “LIKE ‘_at’” would return “At”, “Eat”, “Cat”, “Hat”, “Sat”.. etc.
Square Brackets ([ ]) is a third option. Within the square brackets you specify a range or set of characters which will replace a single character within a string. There are a couple of ways to use the square brackets as below;
All of the above would return the exact same results. In example one, we are specifying all of the characters we would like to look for in our string. Example two is exactly the same, but has comma’s between the letters to improve readability. Example three is slightly different, instead of specifying the individual characters, we are using a range, which will find anything that equals the two characters you input, and anything inbetween.
The above square bracket examples are actually fairly useless unless you store single letters in a column. To get around this, you can combine the symbols to give you more flexibility in your search.
LIKE ‘[a-e]%’ would return anything that starts with the letters a-e (Air, Beer, Cricket, Dozen, Easy are all examples of what this search would return).
LIKE ‘[0-9][0-9][0-9]‘ can be used to confirm that a value is a 3 digit number.
The last symbol available to the LIKE clause is the caret (^). This can be used in conjuction with square brackets to rule out a set or range of characters.
LIKE ‘[^a-e]%’ will return anything that starts with F-Z.
The NOT key word can also be specified with the LIKE clause to do a wildcard search for things that aren’t similar to your search conditions
NOT LIKE ‘%Stagers%’ would return anything that doesn’t contain the character string “Stagers”.
The NOT keyword also gives you the ability to create a double negative! Again, this is a little pointless, but it’s possible!
NOT LIKE ‘[^a-d]%’ would return anything that starts with an A, B, C, or D.