How can I escape square brackets in a LIKE clause?

Question

I am trying to filter items with a stored procedure using like. The column is a varchar(15). The items I am trying to filter have square brackets in the name.

For example: WC[R]S123456.

If I do a LIKE 'WC[R]S123456' it will not return anything.

I found some information on using the ESCAPE keyword with LIKE, but how can I use it to treat the square brackets as a regular string?

Answer 1

As your pattern does not have any wild cards apart from accidental ones that you want to escape it is unclear why you are using LIKE here at all (as opposed to = 'WC[R]S123456' with no need to escape anything.

If in reality you are looking for strings containing the substring 'WC[R]S123456' and so are appending leading and trailing % then it still may be easier to just use

WHERE CHARINDEX('WC[R]S123456' , YourColumn) > 0

So again there is no need to escape anything. This isn't sargable but neither are LIKE clauses with leading wildcards.

If you do need to use LIKE for some reason not stated then there is one reason to prefer the syntax with the ESCAPE clause (apart from readability)

For the example data

CREATE TABLE MyTable
(
YourColumn VARCHAR(50) INDEX IX_YourColumn
)

INSERT MyTable (YourColumn)
SELECT 
    CASE 
        WHEN value <= 250 THEN 'WC[R]S123456'
        WHEN value <= 500 THEN 'WC[R]S1234567'
        WHEN value <= 750 THEN 'WC[R]S12345678'
        ELSE 'WC[R]S123456789'
    END
FROM generate_series(1,1000)

The execution plan for the LIKE 'WC\[R]S123456' ESCAPE '\' variant looks fine. It gets converted to a range seek on YourColumn >= 'WC[R]S123456' AND YourColumn <= 'WC[R]S123456' and estimated number of rows is accurate and the seek reads exactly the rows matching the predicate.

The LIKE 'WC[[]R]S123456' variant is not quite as efficient. This gets converted to a seek on YourColumn >= 'WC[R]S123456' and YourColumn < 'WC[R]S123457' with a residual predicate evaluating the LIKE.

In this (contrived) example the whole table is within that range and all 1,000 rows were read.

Answer 2

There is a problem in that while

LIKE 'WC[[]R]S123456'

and

LIKE 'WC\[R]S123456' ESCAPE '\'

both work for SQL Server, neither work for Oracle.

It seems that there isn't any ISO/IEC 9075 way to recognize a pattern involving a left brace.

Answer 3

Use the following.

For user input to search as it is, use escape, in that it will require the following replacement for all special characters (the below covers all of SQL Server).

Here a single quote, "'" ,is not taken as it does not affect the like clause as it is a matter of string concatenation.

The "-" & "^" & "]" replace is not required as we are escaping "[".

String FormattedString = "UserString".Replace("ð","ðð").Replace("_", "ð_").Replace("%", "ð%").Replace("[", "ð[");

Then, in SQL Query it should be as following. (In parameterised query, the string can be added with patterns after the above replacement).

To search an exact string.

like 'FormattedString' ESCAPE 'ð'

To search start with a string:

like '%FormattedString' ESCAPE 'ð'

To search end with a string:

like 'FormattedString%' ESCAPE 'ð'

To search containing with a string:

like '%FormattedString%' ESCAPE 'ð'

And so on for other pattern matching. But direct user input needs to be formatted as mentioned above.

Answer 4

Let's say you want to match the literal its[brac]et.

You don't need to escape the ] as it has special meaning only when it is paired with [.

Therefore escaping [ suffices to solve the problem. You can escape [ by replacing it with [[].

Answer 5

According to documentation:

You can use the wildcard pattern matching characters as literal characters. To use a wildcard character as a literal character, enclose the wildcard character in brackets.

You need to escape these three characters %_[:

'5%'      LIKE '5[%]'      -- true
'5$'      LIKE '5[%]'      -- false
'foo_bar' LIKE 'foo[_]bar' -- true
'foo$bar' LIKE 'foo[_]bar' -- false
'foo[bar' LIKE 'foo[[]bar' -- true
'foo]bar' LIKE 'foo]bar'   -- true

Answer 6

If you would need to escape special characters like '_' (underscore), as it was in my case, and you are not willing/not able to define an ESCAPE clause, you may wish to enclose the special character with square brackets '[' and ']'.

This explains the meaning of the "weird" string '[[]' - it just embraces the '[' character with square brackets, effectively escaping it.

My use case was to specify the name of a stored procedure with underscores in it as a filter criteria for the Profiler. So I've put string '%name[_]of[_]a[_]stored[_]procedure%' in a TextData LIKE field and it gave me trace results I wanted to achieve.

Here is a good example from the documentation: LIKE (Transact-SQL) - Using Wildcard Characters As Literals

Answer 7

LIKE 'WC[[]R]S123456' 

or

LIKE 'WC\[R]S123456' ESCAPE '\'

Should work.

Answer 8

Instead of '\' or another character on the keyboard, you can also use special characters that aren't on the keyboard. Depending o your use case this might be necessary, if you don't want user input to accidentally be used as an escape character.

Answer 9

Here is what I actually used:

like 'WC![R]S123456' ESCAPE '!'

Answer 10

The ESCAPE keyword is used if you need to search for special characters like % and _, which are normally wild cards. If you specify ESCAPE, SQL will search literally for the characters % and _.

Here's a good article with some more examples

SELECT columns FROM table WHERE 
    column LIKE '%[[]SQL Server Driver]%' 

-- or 

SELECT columns FROM table WHERE 
    column LIKE '%\[SQL Server Driver]%' ESCAPE '\'

Answer 11

I needed to exclude names that started with an underscore from a query, so I ended up with this:

WHERE b.[name] not like '\_%' escape '\'  -- use \ as the escape character