5

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.

anar khalilov
  • 16,993
  • 9
  • 47
  • 62

3 Answers3

3

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.

Szymon
  • 42,577
  • 16
  • 96
  • 114
  • Sorry for deleting and opening this answer again but my first try wasn't correct. It's not that easy to come up with an example for that. – Szymon Nov 21 '13 at 11:23
1

No one has mentioned this from what I read, but if you wanted to also query for text containing a literal '[]' you'd be forced to escape characters. So, let's say you wanted to query for the string containing the literal characters 'abc[]'. I believe you'd be forced to construct a query like this:

SELECT 'MATCHED! (Szymon)' WHERE @TEST_STRING LIKE '%[abc![!]]%' ESCAPE '!';

Here's another link on the subject: How can I escape square brackets in a LIKE clause?

Community
  • 1
  • 1
user1152283
  • 31
  • 2
0

Escape has a possibility to escape characters in string matching context and in [] context. 

LIKE '%[1-5!%]%' ESCAPE '!'

This will match '1' or '1%' or '4' or '4%', but not 6 nor 6%

evhen14
  • 1,839
  • 12
  • 16
  • You can always use `[%]` in such a case. – Szymon Nov 21 '13 at 11:27
  • 1
    Yes, but they have different meanings. [] used to specify range of possible values, whereas escape for escaping like in HTML or URL. But again, yes, you are right, it can be used like that, but not meant to be but SQL Server designers. Maybe escape has better performance, I don't know, but quite possible :) – evhen14 Nov 21 '13 at 11:37