TSQL Like Escape 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

http://sqlserver2000.databases.aspfaq.com/how-do-i-search-for-special-characters-e-g-in-sql-server.html

LIKE 'WC[[R]S123456'

or

LIKE 'WC\[R]S123456'

Should work.

Answer 2

Here is what I actually used:

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

Answer 3

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

Answer 4

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

Answer 5

Can anybody explian is there a speacial meaning for LIKE '%[0001%'. When I filter based on '%[0001%' sql server is not finding any records. But, if I search by '%0001]%' sql server is finding right results.