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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With