Escape a string in SQL Server so that it is safe to use in LIKE expression

Question

How do I escape a string in SQL Server s stored procedure so that it is safe to use in LIKE expression   Suppose I have an NVARCHAR variable like so   declare  myString NVARCHAR 100     And I want to use it in a LIKE expression       WHERE     LIKE        myString          How do I escape the string  more specifically  characters that are meaningful to LIKE pattern matching  e g    or    in T-SQL  so that it is safe to use in this manner   For example  given    myString    aa bb    I want   WHERE     LIKE        somehowEscapedMyString         to match  aa bb    caa bbc  but not  aaxbb  or  caaxbb

User · Answer

Do you want to look for strings that include an escape character? For instance you want this:

select * from table where myfield like '%10%%'.

Where you want to search for all fields with 10%? If that is the case then you may use the ESCAPE clause to specify an escape character and escape the wildcard character.

select * from table where myfield like '%10!%%' ESCAPE '!'

User · Answer

Do you want to look for strings that include an escape character? For instance you want this:

select * from table where myfield like '%10%%'.

Where you want to search for all fields with 10%? If that is the case then you may use the ESCAPE clause to specify an escape character and escape the wildcard character.

select * from table where myfield like '%10!%%' ESCAPE '!'

User · Answer

Had a similar problem  using NHibernate  so the ESCAPE keyword would have been very difficult  and solved it using the bracket characters  So your sample would become  WHERE     LIKE   aa   bb     If you need proof   create table test  field nvarchar 100   go insert test values   abcdef hijklm   insert test values   abcdefghijklm   go select   from test where field like  abcdef   hijklm  go

User · Answer

Rather than escaping all characters in a string that have particular significance in the pattern syntax given that you are using a leading wildcard in the pattern it is quicker and easier just to do   SELECT    FROM YourTable WHERE CHARINDEX  myString   YourColumn   gt  0   In cases where you are not using a leading wildcard the approach above should be avoided however as it cannot use an index on YourColumn   Additionally in cases where the optimum execution plan will vary according to the number of matching rows the estimates may be better when using LIKE with the square bracket escaping syntax when compared to both CHARINDEX and the ESCAPE keyword

User · Answer

Alternative escaping syntax      LIKE Wildcard Literals      The JDBC driver supports the  escape  escape character   syntax for using LIKE clause wildcards as literals    SELECT   FROM tab WHERE col LIKE  a  c   escape         db lt  fiddle demo

User · Answer

You specify the escape character    Documentation here  http   msdn microsoft com en-us library ms179859 aspx

User · Answer

You specify the escape character    Documentation here  http   msdn microsoft com en-us library ms179859 aspx

User · Answer

To escape special characters in a LIKE expression you prefix them with an escape character  You get to choose which escape char to use with the ESCAPE keyword   MSDN Ref   For example this escapes the   symbol  using   as the escape char   select   from table where myfield like   15   off   ESCAPE       If you don t know what characters will be in your string  and you don t want to treat them as wildcards  you can prefix all wildcard characters with an escape char  eg    set  myString   replace                   replace                   replace                   replace   myString                                                                                                                                        Note that you have to escape your escape char too  and make sure that s the inner replace so you don t escape the ones added from the other replace statements   Then you can use something like this    select   from table where myfield like        myString       ESCAPE       Also remember to allocate more space for your  myString variable as it will become longer with the string replacement

User · Answer

Rather than escaping all characters in a string that have particular significance in the pattern syntax given that you are using a leading wildcard in the pattern it is quicker and easier just to do   SELECT    FROM YourTable WHERE CHARINDEX  myString   YourColumn   gt  0   In cases where you are not using a leading wildcard the approach above should be avoided however as it cannot use an index on YourColumn   Additionally in cases where the optimum execution plan will vary according to the number of matching rows the estimates may be better when using LIKE with the square bracket escaping syntax when compared to both CHARINDEX and the ESCAPE keyword

User · Answer

You specify the escape character    Documentation here  http   msdn microsoft com en-us library ms179859 aspx

User · Answer

To escape special characters in a LIKE expression you prefix them with an escape character  You get to choose which escape char to use with the ESCAPE keyword   MSDN Ref   For example this escapes the   symbol  using   as the escape char   select   from table where myfield like   15   off   ESCAPE       If you don t know what characters will be in your string  and you don t want to treat them as wildcards  you can prefix all wildcard characters with an escape char  eg    set  myString   replace                   replace                   replace                   replace   myString                                                                                                                                        Note that you have to escape your escape char too  and make sure that s the inner replace so you don t escape the ones added from the other replace statements   Then you can use something like this    select   from table where myfield like        myString       ESCAPE       Also remember to allocate more space for your  myString variable as it will become longer with the string replacement

User · Answer

Alternative escaping syntax      LIKE Wildcard Literals      The JDBC driver supports the  escape  escape character   syntax for using LIKE clause wildcards as literals    SELECT   FROM tab WHERE col LIKE  a  c   escape         db lt  fiddle demo

User · Answer

To escape special characters in a LIKE expression you prefix them with an escape character  You get to choose which escape char to use with the ESCAPE keyword   MSDN Ref   For example this escapes the   symbol  using   as the escape char   select   from table where myfield like   15   off   ESCAPE       If you don t know what characters will be in your string  and you don t want to treat them as wildcards  you can prefix all wildcard characters with an escape char  eg    set  myString   replace                   replace                   replace                   replace   myString                                                                                                                                        Note that you have to escape your escape char too  and make sure that s the inner replace so you don t escape the ones added from the other replace statements   Then you can use something like this    select   from table where myfield like        myString       ESCAPE       Also remember to allocate more space for your  myString variable as it will become longer with the string replacement

User · Answer

Had a similar problem  using NHibernate  so the ESCAPE keyword would have been very difficult  and solved it using the bracket characters  So your sample would become  WHERE     LIKE   aa   bb     If you need proof   create table test  field nvarchar 100   go insert test values   abcdef hijklm   insert test values   abcdefghijklm   go select   from test where field like  abcdef   hijklm  go

User · Answer

To escape special characters in a LIKE expression you prefix them with an escape character  You get to choose which escape char to use with the ESCAPE keyword   MSDN Ref   For example this escapes the   symbol  using   as the escape char   select   from table where myfield like   15   off   ESCAPE       If you don t know what characters will be in your string  and you don t want to treat them as wildcards  you can prefix all wildcard characters with an escape char  eg    set  myString   replace                   replace                   replace                   replace   myString                                                                                                                                        Note that you have to escape your escape char too  and make sure that s the inner replace so you don t escape the ones added from the other replace statements   Then you can use something like this    select   from table where myfield like        myString       ESCAPE       Also remember to allocate more space for your  myString variable as it will become longer with the string replacement

User · Answer

Do you want to look for strings that include an escape character? For instance you want this:

select * from table where myfield like '%10%%'.

Where you want to search for all fields with 10%? If that is the case then you may use the ESCAPE clause to specify an escape character and escape the wildcard character.

select * from table where myfield like '%10!%%' ESCAPE '!'

User · Answer

You specify the escape character    Documentation here  http   msdn microsoft com en-us library ms179859 aspx

User · Answer

Do you want to look for strings that include an escape character? For instance you want this:

select * from table where myfield like '%10%%'.

Where you want to search for all fields with 10%? If that is the case then you may use the ESCAPE clause to specify an escape character and escape the wildcard character.

select * from table where myfield like '%10!%%' ESCAPE '!'

[sql-server] Escape a string in SQL Server so that it is safe to use in LIKE expression

Examples related to sql-server

Examples related to tsql

Examples related to stored-procedures

Examples related to sql-like