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?

Dactylo

Answer 1

LIKE 'WC[[]R]S123456' 

or

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

Should work.

Conciliatory

The ESCAPE keyword is required if you want to use a custom escape character (the backslash is indeed custom). – Irreplaceable

I corrected the other part of the answer too. SQL Fiddle with before and after versions – Wernerwernerite

note if you want to find open then close bracket, the [[] []] won't work, you have to use the ESCAPE '\' method. – Thanksgiving

If anyone is unclear as to why the bracket needs to be escaped, the documentation for LIKE indicates it is used to match a single character in a range or set. For example, using LIKE '[fz]oo' will match both 'foo' and 'zoo'. – Banerjee

It would be nice to have rationale for both of these in the answer. It was not immediately obvious to me why the first example would work until I read the answer from Amitesh below. – Eun

Prefer LIKE 'WC\[R]S123456' ESCAPE '\' as it is more readable for maintenance. – Waters

I've found that some versions of SQL or variants don't allow the ESCAPE keyword, while others don't allow the [*] selection. I've had to use both in my code. – Interfaith

For me, it looks like, the first proposal would match WC[S123456 or WC]S123456 or WCRS123456. So exactly one of the characters in the brackets would match. It is not the expected result. learn.microsoft.com/en-us/sql/t-sql/language-elements/… – Crackdown

Answer 2

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 [[].

Brittneybrittni

[[] does look weird, but it makes sense when you look at it from the perspective of the parser. The parser has a specific rule for how to handle characters between [ ]. So, the text its[brac]et means: "Find the following consecutive strings: its, (apply rule for square brackets: brac), et". On the other hand, its[[]brac]et means: "Find the following consecutive strings: its, (apply rule for square brackets: [), brac]et". – Publicity

Answer 3

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

Multiplier

I had to use this version (specifying the "escape" character explicitly) - the other answers here didn't give the correct results for me. – Bates

Answer 4

Here is what I actually used:

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

Dactylo

Answer 5

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 '\'

Duda

Answer 6

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

Cutcheon

Answer 7

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

Rosamariarosamond

Answer 8

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.

Maurilia

Brace: { Bracket: [ Parenthesis: ( – Bronchial

Answer 9

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.

Indulge

I often use ¬ - it's still a keyboard character in the UK but rarely used knowingly :) (top left between Esc and Tab ) – Radicle

Users can still submit data containing letters which aren’t on the keyboard. This answer sounds suspiciously like a suggestion to avoid actually solving the problem… – Discriminator

Answer 10

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.

Carminacarminative