CODEWITHSUNDEEP
sql-server-2008
Rudolf Lamprecht
Rudolf Lamprecht

Reputation: 1070

SQL escape special characters when using LIKE clause

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?

Upvotes: 4

Views: 2325

Answers (1)

Szymon
Szymon

Reputation: 43023

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.

Upvotes: 2

Related Questions