SQL escape special characters when using LIKE clause

Question

I am trying to search a column which contains a series of special characters. A separate scalar function was created to simplify the reuse of this functionality:

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

An example of my code is found below:

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

No results are returned when searching for the specified value, but as soon as I remove the square brackets [] the value is returned.

Any idea why my escaped square brackets are not returning a result?

I would guess the reason it won't return any results is because the brackets are used for wildcard character pattern matching and `[]` is an empty set that won't match anything. See [Pattern Matching in Search Conditions](http://technet.microsoft.com/en-us/library/ms187489%28v=sql.105%29.aspx) for reference — jpw, Mar 16 '14 at 10:35
possible duplicate of [Escape a string in SQL Server so that it is safe to use in LIKE expression](http://stackoverflow.com/questions/258757/escape-a-string-in-sql-server-so-that-it-is-safe-to-use-in-like-expression) — FarligOpptreden, Mar 16 '14 at 10:39

Szymon · Accepted Answer · 2014-03-16T10:40:34.497

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.

SQL escape special characters when using LIKE clause

1 Answers1