LIKE | Scratchbox | Zach Stagers

If you need to do a search on a field for a character that’s used by the LIKE syntax, the % for example, then you can specify an ESCAPE Character to allow you search to for the symbol.

To do this, simply specify your escape character, typically a value which you know won’t be returned in the result set, before the character you’re searching for.

For example, if a column containing a percentage was created as a VarChar, and users were inserting the % symbol along with the rate, you might want to find and correct these records before updating the columns data type to a numeric form. To do something like this, you would use a query like the below:

SELECT FirstName,
       Surname,
       TaxBand
 
WHERE TaxBand LIKE '%=%%' ESCAPE '='

This query specifies = as our ESCAPE symbol, therefore the % symbol listed after it can be searched for.

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.

For example,

SELECT FirstName 
FROM People 
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;

LIKE '[abcd]'
LIKE '[a,b,c,d]'
LIKE '[a-d]'

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.

Scratchbox | Zach Stagers

SQL Server Developer and Administrator

Tag Archives: LIKE

SQL LIKE ESCAPE Character

SQL LIKE Clause Syntax