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 I do not understand how to use it to treat the square brackets as a regular string.

Answer 1

LIKE 'WC[[]R]S123456' 

or

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

Should work.

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

Answer 3

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 4

Use Following.

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

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

"-" & "^" & "]" 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, string can be added with patterns after above replacement).

To search exact string.

like 'FormattedString' ESCAPE 'ð'

To search start with string

like '%FormattedString' ESCAPE 'ð'

To search end with string

like 'FormattedString%' ESCAPE 'ð'

To search contain with string

like '%FormattedString%' ESCAPE 'ð'

and so on for other pattern matching. But direct user input needs to format as mentioned above.

Answer 5

Here is what I actually used:

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

Answer 6

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