Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Why is there an ESCAPE argument of the LIKE operator?

If you check out the definition of the LIKE operator, you will see that there is an optional argument named ESCAPE which is described as follows:

Is a character that is put in front of a wildcard character to indicate that the wildcard should be interpreted as a regular character and not as a wildcard. escape_character is a character expression that has no default and must evaluate to only one character.

So when you write a WHERE clause like given below, you tell the SQL Server engine to treat the exclamation mark as an escaping character (just like the '\' character in C#) to list comments that include '30%' substring:

WHERE comment LIKE '%30!%%' ESCAPE '!'

Now here it gets confusing for me. It is already possible to escape wildcard characters by putting them inside square brackets. Why would one want to introduce the ESCAPE argument? It is there for a reason I guess.

EDIT: I see several answers that explain how ESCAPE works. I know that ESCAPE is used to escape wildcard characters but what I also know is that you can escape those wildcard characters using square brackets. I just want to understand what "ESCAPE method" has and "square brackets method" doesn't.

EDIT 2: Although Szymon's example is totally valid, I don't feel this is the only reason ESCAPE was introduced. I might be wrong of course but I suspect there might be some performance based reasons, I don't know for sure. Szymon, You can achieve the same like follows:

DECLARE @TEST_STRING VARCHAR(100) = 'ddd]eee';
SELECT 'MATCHED! (Szymon)' WHERE @TEST_STRING LIKE '%[abc!]]%' ESCAPE '!';
SELECT 'MATCHED! (Altern)' WHERE @TEST_STRING LIKE '%[abc]%' OR @TEST_STRING LIKE '%]%';

As a side note, you don't need to escape the closing square bracket character.

like image 655
anar khalilov Avatar asked Oct 02 '22 09:10

anar khalilov


1 Answers

It is explained a bit further, I think

Also, within the double bracket characters ([ ]), escape characters can be used and the caret (^), hyphen (-), and right bracket (]) can be escaped.

Let's say you want to search for anything that contains characters a, b, c or ].

Since a set of character is enclosed in brackets [ ], you have to escape the closing bracket to make it one of the set:

like '%[abc!]]%' escape '!'

Also, as pointed out in the comments, the syntax to escape using square brackets [ ] is a non-standard feature while ESCAPE is documented.

like image 117
Szymon Avatar answered Oct 12 '22 12:10

Szymon