





The LIKE operator in SQL Server is a really powerful tool. It allows you to specify a pattern to match records in your where clause. You can use the % (percent) as a wildcard value. For example: ‘monk%’ will match ‘monkey’ and ‘monkeys’. But what if you want to match the words ‘60% off sale’ and ‘60% off sales’… you can’t just put ‘60% off sale%’… you need to escape the first %. SQL Server gives us two different ways to escape a special character.
SQL Server LIKE – Exact Single Character
The first way that you can do this is by specifying a specific single character in your pattern. This is done by wrapping the single character in [ ]. The character that you put inside the brackets will tell the system that that character must be found exactly as appears.
1 2 3 |
SELECT SaleDescription FROM SaleInfo WHERE SaleDescription LIKE '60[%] off sale%' |
SQL Server LIKE – Escape Character (read at the bottom of this post to find out what an escape character is)
The second way that you can do this is by specifying an escape character. This is done by using the keyword ESCAPE after your pattern. The literal value of the wildcard character following the escape value will be used instead of the wildcard value. In the example below, we specify that ! is our ESCAPE character in our string. Then we put ! before %. That way the database will look for the literal value of % instead of using that in the wildcard pattern.
1 2 3 |
SELECT SaleDescription FROM SaleInfo WHERE SaleDescription LIKE '60!% off sale%' ESCAPE '!' |
What Is An Escape Character
An escape character is a character that is placed before a character (or string of characters) and it tells the system to read the following character as their literal value. Many systems have reserved characters that act as codes or wildcards in their system. Using an escape character, you tell the system to not read those values as special codes or wildcards.
Reference: https://msdn.microsoft.com/en-us/library/aa933232(v=sql.80).aspx
You could also use:
SELECT SaleDescription
FROM SaleInfo
WHERE SaleDescription LIKE ’60[%] off sale%’
This method had a tendency to truly interested better optimizations since MS had issues optimizing custom escape characters.