在SQL Server中转义字符串，以便可以在LIKE表达式中安全使用

Question

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

假设我有一个NVARCHAR像这样的变量：

declare @myString NVARCHAR(100);

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

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

如何在T-SQL中转义字符串（更具体地说，是对LIKE模式匹配有意义的字符，例如%或?），以便以这种方式安全使用？

例如，给定：

@myString = 'aa%bb'

我想要：

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

匹配'aa%bb'，'caa%bbc'但没有'aaxbb'或'caaxbb'。

Answer 1

要在LIKE表达式中转义特殊字符，请在其前面加上转义字符。您可以选择与ESCAPE关键字一起使用的转义字符。（MSDN参考）例如，使用\作为转义字符，可以转义％符号：select * from table where myfield like '%15\% off%' ESCAPE '\'如果您不知道字符串中将包含哪些字符，并且不想将其视为通配符，则可以在所有通配符前面加上转义字符，例如：set @myString = replace(                 replace(                 replace(                 replace( @myString                ,    '\', '\\' )                ,    '%', '\%' )                ,    '_', '\_' )                ,    '[', '\[' )（请注意，您也必须转义转义字符，并确保这是内部字符，replace以免转义从其他replace语句添加的转义字符）。然后，您可以使用以下内容：select * from table where myfield like '%' + @myString + '%' ESCAPE '\'还要记住为@myString变量分配更多的空间，因为随着字符串替换它会变长。

Answer 2

遇到类似的问题（使用NHibernate，因此ESCAPE关键字将非常困难），并使用方括号字符解决了该问题。所以你的样本会变成WHERE ... LIKE '%aa[%]bb%'如果您需要证明：create table test (field nvarchar(100))goinsert test values ('abcdef%hijklm')insert test values ('abcdefghijklm')goselect * from test where field like 'abcdef[%]hijklm'go

Answer 3

假设您在模式中使用了前导通配符，则与其转义在模式语法中具有特殊意义的字符串中的所有字符，不如将它更快，更容易地完成。SELECT * FROM YourTableWHERE CHARINDEX(@myString , YourColumn) > 0如果您不使用前导通配符，则应避免使用上述方法，因为它不能在上使用索引YourColumn。此外在情况下，最佳的执行计划将根据使用时的估计可能是更好的匹配的行的数目而变化LIKE与方形支架相比时逸出语法两者CHARINDEX和所述ESCAPE关键字。