在 SQL Server 中对字符串进行转义，以便在 LIKE 表达式中安全使用 [英] Escape a string in SQL Server so that it is safe to use in LIKE expression

问题描述

如何在 SQL Server 的存储过程中对字符串进行转义，以便在 LIKE 表达式中安全使用.

How do I escape a string in SQL Server's stored procedure so that it is safe to use in LIKE expression.

假设我有一个 NVARCHAR 变量，如下所示:

Suppose I have an NVARCHAR variable like so:

declare @myString NVARCHAR(100);

我想在 LIKE 表达式中使用它:

And I want to use it in a LIKE expression:

... WHERE ... LIKE '%' + @myString + '%';

如何转义 T 中的字符串(更具体地说，对 LIKE 模式匹配有意义的字符，例如 % 或 ?)-SQL，这样使用起来安全吗?

How do I escape the string (more specifically, characters that are meaningful to LIKE pattern matching, e.g. % or ?) in T-SQL, so that it is safe to use in this manner?

例如，给定:

@myString = 'aa%bb'

我想要:

WHERE ... LIKE '%' + @somehowEscapedMyString + '%'

匹配 'aa%bb'、'caa%bbc' 但不匹配 'aaxbb' 或 'caaxbb'.

to match 'aa%bb', 'caa%bbc' but not 'aaxbb' or 'caaxbb'.

推荐答案

要对 LIKE 表达式中的特殊字符进行转义，请在它们前面加上转义字符.您可以选择与 ESCAPE 关键字一起使用的转义字符.(MSDN 参考)

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
                ,    '\', '\\' )
                ,    '%', '\%' )
                ,    '_', '\_' )
                ,    '[', '\[' )

(请注意，您也必须转义您的转义字符，并确保它是内部的 replace，这样您就不会转义从其他 replace 语句中添加的那些).然后你可以使用这样的东西:

(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 '\'

还要记住为@myString 变量分配更多空间，因为它会随着字符串替换而变长.

Also remember to allocate more space for your @myString variable as it will become longer with the string replacement.

这篇关于在 SQL Server 中对字符串进行转义，以便在 LIKE 表达式中安全使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！