I LIKE it with ESCAPE

Most of you who are in TSQL world like me have used LIKE operator to find strings that not exactly match or find a row which contains a specific word.

For example, if you want to find names that starts with "ST", you would do someting like this in TSQL:

SELECT DisplayName FROM dbo.Users

WHERE DisplayName LIKE 'ST%'

This will bring display names like "Stephen", "Stanley", "Stone" etc.

But recently, I came across neat trick I can use with LIKE operator in TSQL, specially when using wild card charaters.

LIKE operator accept several wild card characters:

• % matches any string of zero or more characters (this is the most used)
• _ matches exactly one character
• LIKE 'ST_' => matches => ST5, STT, ST3, etc (exactly one character after ST)
• [...] matches any single character in a set or range (like [a-f])
• LIKE 'ST[a-f]phen => matches => STaphen, STbphe, STcphen, STdphen, STephen, STfphen (characters from a to f)
• [^...] matches any single character not in a set or range
• Similar to above, but it is NOT match

When you using wild card characters like this, what if search string contains, wild card characters in it and you want to search fo them?

That is where ESCAPE hint comes handy.

For example let us consider following:

SELECT * FROM Products WHERE ProductCode LIKE '%_DISCONTINUED%';

In here we want to search for product codes, ended with "_DISCONTINUED" E.g. CAT1_DISCOUNTINUED, LOSS_DISCOUNTINUED. In other words we need "_" character (which LIKE operator consider as wild card character) in the search string.

In that scenario, we can change the T-SQL like below:

SELECT * FROM Products WHERE ProductCode LIKE '%\_DISCONTINUED%' ESCAPE '\';

This tells SQL parser, we are using "\" character as escape character and we escaping "_"character and telling LIKE operator to include it in the search.

If we don't use escape character, LIKE "%_DISCOUNTINUED%" will result in results like "ADISCOUNINUED", "IGNOREMEDISCOUNTINUED" which we really don't want.

Now \_ means "a literal underscore," and the query only matches rows where that underscore is actually there. The ESCAPE '\' clause is what tells SQL Server "hey, whenever you see a backslash in this pattern, treat the next character as literal, not special."

You don't always have to use ackslash, by the way — any single character works, as long as it's one you're not otherwise using in the pattern:

SELECT * FROM Products WHERE ProductCode LIKE '%!_DISCONTINUED%' ESCAPE '!';

Above T-SQL also brings same results.

Consider a example you want to find values like 50%.

SELECT * FROM Discounts WHERE Description LIKE '%50\%%' ESCAPE '\';

In above example you can see two % signs together, one sign tells SQL server next character appear need escaping from wild card treatment, there fore looking for 50%.

This setting is per query, andn will not effect all queries you execute after that.

Hope you have learned something new today like me.