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

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

User · Answer

Here is what I actually used   like  WC  R S123456  ESCAPE

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

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

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

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

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

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

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

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

Here is what I actually used   like  WC  R S123456  ESCAPE

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

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

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

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

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

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

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

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

User · Answer

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

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