0

I'm creating a stored procedure that accepts a string parameter used for searching a table. Following is an example similar to what I'm doing.

select * from Logs where IDString like @Prefix+ '[-]%'

The problem is the @Prefix parameter may contain wildcards such as % or _ so I need to escape those.

Besides using Replace() to escape them myself, is there a standard way to do that in SQL Server 2016?

stormtrooper
  • 340
  • 2
  • 18

4 Answers4

0

If you are using .Net, you can achieve it by using Regex

public static string MakeSqlLikeSearchParam(string input) =>
  string.IsNullOrWhiteSpace(input) ? string.Empty : "%" + EscapeSqlWildcard(input) + "%";

private static string EscapeSqlWildcard(string input) => 
  new Regex("(\\\\|%|\\[|\\]|_)").Replace(input, "\\$1");

In SQL Server, you can read the link below to have a better understanding

SQL LIKE query: escape wildcards

Nguyễn Văn Phong
  • 13,506
  • 17
  • 39
  • 56
0

You can prefix all the wildcards with '\'

set @Prefix = replace( 
                replace( 
                replace( 
                replace( @Prefix
                ,    '\', '\\' )
                ,    '%', '\%' )
                ,    '_', '\_' )

And then use the following to escape all the wild cards using

ESCAPE

select * from Logs where IDString like @Prefix ESCAPE '\'
Naveen Kumar
  • 1,988
  • 1
  • 7
  • 11
0

Need to insert on each special character a escape character. As you said, you can stack a bunch of REPLACE functions:

DECLARE @Prefix VARCHAR(100) = 'My%Prefix_'

DECLARE @Replaced VARCHAR(100) = REPLACE(REPLACE(REPLACE(REPLACE(@Prefix, '%', '\%'), '^', '\^'), '_', '\_'), '[', '\[')

SELECT 
    Original = @Prefix,
    Translated = @Replaced

Result:

Original    Translated
My%Prefix_  My\%Prefix\_

Then use the ESCAPE clause for the LIKE to tell the engine that your character is the escape indicator:

SELECT * 
FROM Logs 
WHERE IDString LIKE @Replaced + '[-]%' 
ESCAPE '\'

You could also create a scalar function with the replaces:

CREATE FUNCTION dbo.ufnEscapeLikeSpecialCharacters(@Input VARCHAR(100), @EscapeCharacter CHAR)
RETURNS VARCHAR(100)
AS
BEGIN
    RETURN REPLACE(REPLACE(REPLACE(REPLACE(@Input, '%', @EscapeCharacter + '%'), '^', @EscapeCharacter + '^'), '_', @EscapeCharacter + '_'), '[', @EscapeCharacter + '[')
END

And then use it like:

DECLARE @Prefix VARCHAR(100) = 'My%Prefix_'

SELECT * 
FROM Logs 
WHERE IDString LIKE dbo.ufnEscapeLikeSpecialCharacters(@Prefix, '\') + '[-]%' 
ESCAPE '\'
EzLo
  • 13,780
  • 10
  • 33
  • 38
0

From what I can tell, you want to get values where IDstring starts with @Prefix + @strParam. In order to do that, you don't need regular expressions, just check the index of a substring:

select * from Logs 
where charindex(@Prefix + '[-]%', IDString) = 1

Here's general way of escaping characters (comments in code):

declare @str varchar(100) = 'some%string%with_special[chars',
        @escapeChar varchar(50) = '\'
-- escape every character in string
;with cte as (
    select len(@str) [idx], @escapeChar + substring(@str, len(@str), 1) escapedChar
    union all
    select [idx] - 1, @escapeChar + substring(@str, [idx] - 1, 1) from cte
    where [idx] > 1
)
-- concatenate all escaped characters
SELECT @str = CAST((
    SELECT [text()] = '' + escapedChar
    FROM cte
    ORDER BY idx
    FOR XML PATH(''), TYPE) AS
VARCHAR(1000))
-- test the string
select 1
where 'some%string%with_special[chars' like @str escape @escapeChar
Michał Turczyn
  • 32,028
  • 14
  • 47
  • 69