297

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?

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Travis
  • 3,389
  • 2
  • 19
  • 11

10 Answers10

403
LIKE 'WC[[]R]S123456'

or

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

Should work.

Otávio Décio
  • 73,752
  • 17
  • 161
  • 228
  • 11
    The ESCAPE keyword is required if you want to use a custom escape character (the backslash is indeed custom). – Ryan Kohn Oct 30 '12 at 14:59
  • 2
    I corrected the other part of the answer too. [SQL Fiddle with before and after versions](http://www.sqlfiddle.com/#!3/d41d8/5633) – Martin Smith Oct 31 '12 at 22:20
  • note if you want to find open then close bracket, the [[] []] won't work, you have to use the ESCAPE '\' method. – user420667 Oct 21 '16 at 23:00
  • 13
    If anyone is unclear as to why the bracket needs to be escaped, the [documentation for LIKE](https://msdn.microsoft.com/en-us/library/ms179859.aspx) 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'. – Holistic Developer Feb 01 '17 at 17:18
  • 5
    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. – Don Jewett Jan 04 '19 at 17:59
  • 2
    Prefer `LIKE 'WC\[R]S123456' ESCAPE '\'` as it is more readable for maintenance. – Fire Druid Dec 05 '19 at 13:10
  • 1
    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. – Marius Nov 04 '20 at 20:17
  • 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. https://learn.microsoft.com/en-us/sql/t-sql/language-elements/like-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15#using-wildcard-characters-as-literals – iuzuz Sep 27 '21 at 09:07
156

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

beercohol
  • 2,577
  • 13
  • 26
Amitesh
  • 1,569
  • 1
  • 9
  • 2
  • 2
    `[[]` 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`"_. – Brian May 25 '18 at 18:26
30

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
Andrew
  • 8,322
  • 2
  • 47
  • 70
  • 3
    I had to use this version (specifying the "escape" character explicitly) - the other answers here didn't give the correct results for me. – MarcE May 03 '12 at 15:35
23

Here is what I actually used:

like 'WC![R]S123456' ESCAPE '!'
Jason Plank
  • 2,336
  • 5
  • 31
  • 40
Travis
  • 3,389
  • 2
  • 19
  • 11
18

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 '\'
Jeff Atwood
  • 63,320
  • 48
  • 150
  • 153
scottm
  • 27,829
  • 22
  • 107
  • 159
7

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
Salman A
  • 262,204
  • 82
  • 430
  • 521
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

ssurba
  • 99
  • 2
  • 4
4

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.

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
questaware
  • 41
  • 2
2

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.

Rob Breidecker
  • 604
  • 1
  • 7
  • 12
  • 2
    I often use `¬` - it's still a keyboard character in the UK but rarely used knowingly :) (top left between `Esc` and `Tab` ) – Andi Mohr Dec 09 '15 at 12:21
  • 1
    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… – binki Apr 11 '18 at 01:58
0

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.

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Anonymous Creator
  • 2,968
  • 7
  • 31
  • 77