Escaping the Escape Character Does Not Work - SQL Like Operator

Escaping the escape character does not work – SQL LIKE Operator

Modify your CustomFormat method like this:

private static string CustomFormat(string input)
{
    input = input.Replace(@"\", @"\\"); 
    input = input.Replace(@"%", @"\%");
    input = input.Replace(@"[", @"\[");
    input = input.Replace(@"]", @"\]");
    input = input.Replace(@"_", @"\_");
    return input;
}

Escaping special characters in a SQL LIKE statement using sql parameters

You have two options:

• enclose them in [ and ]. So:

  where pattern like '[%]'
  

  Looks for the percentage character.
  Full list of characters to escape - '_', '%', '[', ']' with corresponding replacements '[_]', '[%]', '[[]', '[]]'. Sample code can be found in Escaping the escape character does not work – SQL LIKE Operator

• use an escape character that is unlikely to be in the string, such as a backtick:

  where pattern like '`%' escape '`'
  

  (See the syntax on MSDN - LIKE (Transact-SQL).)

In both cases, I would suggest that you make the substitution in the application layer, but you can also do it in SQL if you really want:

where pattern like replace(@pattern, '%', '[%]')

And, giving the end-user access to wildcards may be a good thing in terms of the user interface.

---

Note: there are couple more special characters '-' and '^' in the LIKE query, but they don't need to be escaped if you are already escaping '[' and ']'.

How to escape a string for use with the LIKE operator in SQL Server?

To search for "%" as a literal not wildcard in a string, it needs escaped as [%].

Now, SQL Server only need 3 characters escaping: % _ [

So, create a scalar udf to wrap this:

REPLACE(REPLACE(REPLACE(@myString, '[', '[[]'), '_', '[_]'), '%', '[%]')

Because of the simplicity (aka: very limited) pattern matching in SQL, nothing more complex is needed...

How do apply SQL like on \detail1\detail2\ (Escaping '\')?

Try this, you need to escape backslashes twice in LIKE statement.

select details from T1 where details like '\\\\%\\\\%\\\\'

http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html

Because MySQL uses C escape syntax in strings (for example, “\n” to represent a newline character), you must double any “\” that you use in LIKE strings. For example, to search for “\n”, specify it as “\\n”. To search for “\”, specify it as “\\\\”; this is because the backslashes are stripped once by the parser and again when the pattern match is made, leaving a single backslash to be matched against.

SQL escape special characters when using LIKE clause

The brackets [] in your query are expanded to [[][]] by your function. Brackets are used to define a character range/set and this way you specify a set of two empty sets. That won't match your string.

---

You can instead adopt a different approach.

If you can find a character that can act as an escape character, you can use it together with ESCAPE keyword in LIKE search.

I modified your function to use CHAR(10) as an escape character (as an example):

ALTER FUNCTION [dbo].[Escape_Special_Character]
( 
@Value nvarchar(max) 
) 
RETURNS nvarchar(max) AS
BEGIN
DECLARE @Result nvarchar(max)
SET @Result = REPLACE( @Value, '[', char(10) + '[' );
SET @Result = REPLACE( @Result, ']', char(10) + ']' );
SET @Result = REPLACE( @Result, '%', char(10) + '%' );
SET @Result = REPLACE( @Result, '*', char(10) + '*' );
SET @Result = REPLACE( @Result, '_', char(10) + '_' );
SET @Result = REPLACE( @Result, '^', char(10) + '^' );
RETURN @Result
END
GO

And then you can do the search like this:

declare @Table table
(
[Value] nvarchar(max)
)
insert into @Table
select
'course name ~!@#$%^&*()_+={}[]\|;'':"<>?,./{|}~ÇüéâäàåçêëèïîìÄÅÉæÆôöòûùÿÖÜ¢£¥áíóúñѺªº¿©¬½¼¡«»°±²³´µ¶·¸¹º»¼½¾'
select * from @Table
where [Value] like '%' + dbo.Escape_Special_Character('course name ~!@#$%^&*()_+={}[]') + '%' 
        ESCAPE char(10)

See more in "Pattern Matching with the ESCAPE Clause" section of this MSDN page.

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

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.

---

Related Topics