如何转义字符串以与 SQL Server 中的 LIKE 运算符一起使用? [英] How to escape a string for use with the LIKE operator in SQL Server?

问题描述

我正在寻找可以在 SQL Server 中工作的类似于 c# 中的 @ 符号的东西，它会导致字符串被视为它的文字.例如:

I am looking for something that works in SQL Server similar to the @ symbol in c# which causes a string to be taken as it's literal. Eg:

string text = "abcd\\efg";
Output of text = abcd\efg

string text = @"abcd\\efg";
Output of text = abcd\\efg

注意 @ 如何影响字符串以按原样使用每个字符.

Note how the @ affected the string to take every character as is.

现在我不确定这是否可行，但这是我的问题，也许有更好的方法来解决这个问题.考虑以下基本查询:

Now I am not sure this is possible but here is my issue and maybe there is a better way to solve this. Consider the following basic query:

SELECT [Name] 
  FROM [Test] 
 WHERE [Name] LIKE (@searchText + '%')

我的问题是他们是否放置了 %、_ 或任何其他可能影响我的 like 子句的特殊字符.我希望匹配就像一个开始于"功能.那么有什么我可以应用到@searchText 说从字面上理解这一点，或者有没有我没有想到的更好的解决方案?

My issue is if they put a %, _ or any other of those special characters that can affect my like clause. I want the match to act just like a 'starts with' function. So is there anything I can apply to the @searchText to say take this literally or is there possbibly a better solution that I am not thinking of?

我不希望解决方案是客户端清理.我需要这个存储过程在不依赖于传入的数据被清理的情况下工作.

I do not want the solution to be client side cleaning. I need this stored proc to work without relying on the data being passed in being cleaned.

推荐答案

要在字符串中搜索%"作为文字而非通配符，需要将其转义为 [%].

To search for "%" as a literal not wildcard in a string, it needs escaped as [%].

现在，SQL Server 只需要转义 3 个字符:% _ [

Now, SQL Server only need 3 characters escaping: % _ [

因此，创建一个标量 udf 来包装它:

So, create a scalar udf to wrap this:

REPLACE(REPLACE(REPLACE(@myString, '[', '[[]'), '_', '[_]'), '%', '[%]')

由于 SQL 中的模式匹配简单(又名:非常有限)，不需要更复杂的...

Because of the simplicity (aka: very limited) pattern matching in SQL, nothing more complex is needed...

这篇关于如何转义字符串以与 SQL Server 中的 LIKE 运算符一起使用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！