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

User · Answer

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 · Answer

LIKE  WC   R S123456     or  LIKE  WC  R S123456  ESCAPE       Should work

User · Answer

Here is what I actually used   like  WC  R S123456  ESCAPE

User · Answer

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 · Answer

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 · Answer

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 · Answer

Here is what I actually used   like  WC  R S123456  ESCAPE

User · Answer

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

User · Answer

LIKE  WC   R S123456     or  LIKE  WC  R S123456  ESCAPE       Should work

User · Answer

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 · Answer

LIKE  WC   R S123456     or  LIKE  WC  R S123456  ESCAPE       Should work

User · Answer

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   40 Transact-SQL  41  - Using Wildcard Characters As Literals

User · Answer

Here is what I actually used   like  WC  R S123456  ESCAPE

User · Answer

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 · Answer

Here is what I actually used   like  WC  R S123456  ESCAPE

User · Answer

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 · Answer

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 · Answer

LIKE  WC   R S123456     or  LIKE  WC  R S123456  ESCAPE       Should work

User · Answer

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    -   amp       amp      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 · Answer

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    -   amp       amp      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 · Answer

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 · Answer

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 · Answer

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 · Answer

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   40 Transact-SQL  41  - Using Wildcard Characters As Literals

User · Answer

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

User · Answer

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

[sql-server] How can I escape square brackets in a LIKE clause?

Examples related to sql-server

Examples related to tsql

Examples related to escaping

Examples related to character

Examples related to sql-like