在SQL Server中搜索具有特殊字符的行

While troubleshooting a programming problem today I noticed that you can’t use a LIKE search for string columns containing special characters like % or _ without using a special syntax. Figuring out the problem took only a few minutes, but remembering syntax is always easier if you write about it.

今天在解决编程问题时，我注意到您不能使用LIKE搜索包含特殊字符(如％或_)的字符串列，而无需使用特殊语法。 解决问题仅花费了几分钟，但是如果您编写语法，记住语法总是容易的。

So yes, this post is solely for my benefit. Hopefully it will help somebody else too.

是的，这篇文章仅出于我的利益。 希望它也会对其他人有所帮助。

Let’s say you want to find any fields that contain the text “100%”, so you put together this query:

假设您要查找包含文本“ 100％”的任何字段，因此将以下查询放在一起：

SELECT * FROM tablename WHERE fieldname LIKE ‘%100%%’

SELECT * FROM tablename WHERE fieldname LIKE'％100 %%'

Instead of what you wanted, you’ll get all the rows that contain “100” as well as the rows that contain “100%”.

代替您想要的，您将获得包含“ 100”的所有行以及包含“ 100％”的行。

The problem here is that SQL Server uses the percent sign, underscore, and square brackets as special characters. You simply can’t use them as a plain character in a LIKE query without escaping them.

这里的问题是SQL Server使用百分号，下划线和方括号作为特殊字符。 您只能在不进行转义的情况下将它们用作LIKE查询中的普通字符。

Square Bracket Escape

方括号逃生

You can surround the % or _ with square brackets to tell SQL Server that the character inside is a regular character.

您可以将％或_用方括号括起来，以告诉SQL Server里面的字符是常规字符。

SELECT * FROM tablename WHERE fieldname LIKE ‘%100[%]%’

SELECT * FROM表名WHERE字段名如'％100 [％]％'

T-SQL ESCAPE Syntax

T-SQL ESCAPE语法

Alternatively, you can append the ESCAPE operator onto your query, and add a \ character before the value you want to escape.

或者，您可以将ESCAPE运算符附加到查询中，并在要转义的值之前添加\字符。

SELECT * FROM tablename WHERE fieldname LIKE ‘%100\%%’ ESCAPE ‘\’

SELECT * FROM tablename WHERE fieldname LIKE'％100 \ %%'ESCAPE'\'

The ESCAPE ‘\’ part of the query tells the SQL engine to interpret the character after the \ as a literal character instead of as a wildcard.

查询的ESCAPE'\'部分告诉SQL引擎将\之后的字符解释为文字字符而不是通配符。

Personally I find the second method easier to deal with, and you can use it to escape a square bracket as well.

我个人认为第二种方法更易于处理，您也可以使用它来避开方括号。

翻译自: https://www.howtogeek.com/howto/the-geek-blog/search-for-rows-with-special-characters-in-sql-server/