How can I escape square brackets in a LIKE clause?

Travis_7316 0
Viewed 198537

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.

character sql-like escaping tsql sql-server
10 Answers
LIKE 'WC[[]R]S123456'

or

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

Should work.

user_486840

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

user_5797120

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
user_151270

Here is what I actually used:

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

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

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
user_870150

There is a problem in that whilst:

LIKE 'WC[[]R]S123456'

and:

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

Both work for SQL Server but neither work for Oracle.

It seems that there is no ISO/IEC 9075 way to recognize a pattern involving a left brace.

user_107557560

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.

user_92634180
Related