如何避免在SQL Server中使用LIKE操作符的字符串?

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:

我正在寻找一些在SQL Server中工作的东西，类似于c#中的@符号，它导致字符串被当作文字。例如:

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 + '%')

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?

我的问题是，如果他们把%，_或其他任何可以影响我喜欢的条款的特殊字符。我希望匹配函数的作用就像'start with'函数一样。那么有什么东西我可以应用到@searchText中去，从字面上理解或者有更好的解决方案我没有想到?

Edit: 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.

编辑:我不希望解决方案是客户端清洗。我需要这个存储的proc来工作，而不用依赖正在被清理的数据。

7 个解决方案

#1

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

要将“%”作为字符串中的文字而不是通配符进行搜索，需要将其转义为[%]。

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

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

So, create a scalar udf to wrap this:

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

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

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

由于SQL中的简单模式匹配(aka: very limited)，不需要任何更复杂的东西……

#2

In TSQL, you can wrap the % and _ characters in brackets like so [%] [_] this tells SQL to treat them as literals.

在TSQL中，您可以将%和_字符括在括号中，如so[%][_]，这告诉SQL将它们视为常量。

I have tested and verified this works in SQL Server 7.0, 2000, and 2005.

我已经在SQL Server 7.0、2000和2005中测试并验证了这一点。

http://msdn.microsoft.com/en-us/library/aa933232(SQL.80).aspx

http://msdn.microsoft.com/en-us/library/aa933232(SQL.80). aspx

#3

Each character to be treated literally should be enclosed in square brackets. A right bracket is taken literally directly so don't enclose that one.

每个要处理的字符都应该用方括号括起来。右括号是直接取的，所以不要把它括起来。

#4

If you parameterize your query you don't need to worry about it.

如果对查询进行参数化，就不需要担心这个问题。

UPDATE

更新

As recursive stated in the comments, % still needs to be escaped even in parameterized queries, I didn't realize linq to sql was doing it automagically when I tested.

正如评论中提到的递归，即使在参数化查询中也需要转义%，我在测试时没有意识到linq to sql是自动执行的。

You can use ESCAPE 'x' where x is the character you wish to be the escape character. Linq to SQL does it like this

您可以使用ESCAPE 'x'，其中x是您希望成为转义字符的字符。Linq to SQL这样做。

WHERE [Name] LIKE @searchText ESCAPE '~'

如@searchText ESCAPE '~'

where @searchText = [some text with a~% character%]

其中@searchText =[一些字符% ~%的文本]

or as others have stated it can be escaped with [%]

或者如其他人所说，可以用[%]来转义

view the documentation

查看文档

#5

-1

I'd sanitize the string in the front-end application rather than try and do hokey stuff in SQL to work around this.

我将在前端应用程序中对字符串进行消毒，而不是尝试在SQL中使用hokey来解决这个问题。

#6

-1

From the docs:

从文档:

Syntax

语法

match_expression [ NOT ] LIKE pattern [ ESCAPE escape_character ]

match_expression [NOT]类似模式[ESCAPE escape_character]

Use the ESCAPE option like so:

使用ESCAPE选项，如下所示:

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

#7

-1

If you don't want to modify the incoming text, you can use the "LEFT" function to create your own "STARTSWITH":

如果不想修改输入文本，可以使用“左”函数创建自己的“STARTSWITH”:

SELECT [Name] 
  FROM [Test] 
 WHERE @searchText = LEFT( [Name], LEN( @searchText ) )

(Note that you probably need to do extra work to handle the case of NULLs or empty string.)

(注意，您可能需要做额外的工作来处理空字符串或空字符串。)

EDIT: Removed the incorrect statement about using "LIKE" to search for "%".

编辑:删除使用“LIKE”搜索“%”的错误语句。

#1