How to Escape a String for Use with the Like Operator in SQL Server

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...

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.

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.

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 ']'.

T-SQL special characters to escape for LIKE operator wildcard search

It looks like you got them all, although I think escaping ']' is unnecessary. Technically you should just need to escape the opening bracket ('[').

DECLARE @Table1 TABLE
(
   Column1 VARCHAR(32) NOT NULL PRIMARY KEY
);

INSERT @Table1(Column1)
VALUES
   ('abc%def'),
   ('abc_def'),
   ('abc[d]ef'),
   ('abc def'),
   ('abcdef');

DECLARE @p VARCHAR(32) = 'abc*]*';

DECLARE @Escaped VARCHAR(64) = REPLACE(@p, '[', '[[]');
SET @Escaped = REPLACE(@Escaped, '_', '[_]');
SET @Escaped = REPLACE(@Escaped, '%', '[%]');
SET @Escaped = REPLACE(@Escaped, '*', '%');

SELECT T.Column1
FROM @Table1 T
WHERE T.Column1 LIKE @Escaped;

How to properly escape user input for the SQL LIKE operator? (Postgres)

If you don't want the use to use wildcards, don't use like. Instead:

where position(? in name) > 0

Note that ? is a parameter placeholder so you don't have to munge the query string with (dangerous) user input.

Use LIKE (or better yet regular expressions) if you want users to take advantage of the wildcards. Otherwise, I don't see an advantage to that.

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;
}

---

Related Topics