我如何能在类似的子句中避免方括号?

I am trying to filter items with a stored procedure using like. The column is a varchar(15). The items I am trying to filter have square brackets in the name.

我正在尝试使用类似的存储过程过滤项目。该列是一个varchar(15)。我要筛选的项目名称中有方括号。

For example: WC[R]S123456.

例如:WC S123456[R]。

If I do a LIKE 'WC[R]S123456' it will not return anything.

如果我像“WC[R]S123456”那样做，它不会返回任何东西。

I found some information on using the ESCAPE keyword with LIKE but I do not understand how to use it to treat the square brackets as a regular string.

我找到了一些使用ESCAPE关键字的信息，但是我不知道如何使用它来将方括号作为常规字符串处理。

8 个解决方案

#1

228

LIKE 'WC[[]R]S123456'

或

LIKE 'WC\[R]S123456' ESCAPE '\'

Should work.

应该工作。

#2

Let's say you want to match the literal its[brac]et.

假设你想要匹配文字的[brac]et。

You don't need to escape the ] as it has special meaning only when it is paired with [.

你不需要逃避，因为只有当它与【同】时，它才有特殊意义。

Therefore escaping [ suffices to solve the problem. You can escape [ by replacing it with [[].

因此，逃避(解决问题)。你可以把它换成[[]。

#3

I needed to exclude names that started with an underscore from a query, so I ended up with this:

我需要排除从查询中开始的下划线的名称，所以我最终得到了这个:

WHERE b.[name] not like '\_%' escape '\'  -- use \ as the escape character

#4

Here is what I actually used:

下面是我实际使用的:

like 'WC![R]S123456' ESCAPE '!'

#5

The ESCAPE keyword is used if you need to search for special characters like % and _, which are normally wild cards. If you specify ESCAPE, SQL will search literally for the characters % and _.

如果需要搜索%和_等特殊字符(通常是通配符)，则使用ESCAPE关键字。如果指定ESCAPE，则SQL将按字面意思搜索字符%和_。

Here's a good article with some more examples

这里有一篇好文章，还有更多的例子。

SELECT columns FROM table WHERE 
    column LIKE '%[[]SQL Server Driver]%' 

-- or 

SELECT columns FROM table WHERE 
    column LIKE '%\[SQL Server Driver]%' ESCAPE '\'

#6

Instead of '\' or another character on the keyboard, you can also use special characters that aren't on the keyboard. Depending o your use case this might be necessary, if you don't want user input to accidentally be used as an escape character.

除了键盘上的“\”或其他字符，你还可以使用键盘上没有的特殊字符。根据您的用例，这可能是必要的，如果您不希望用户输入意外被用作转义字符。

#7

If you would need to escape special characters like '_' (underscore), as it was in my case, and you are not willing/not able to define an ESCAPE clause, you may wish to enclose the special character with square brackets '[' and ']'.

如果您需要转义像“_”(下划线)这样的特殊字符，就像在我的例子中一样，您不愿意/无法定义一个escape子句，您可能希望用方括号['和']将这个特殊字符括起来。

This explains the meaning of the "weird" string '[[]' - it just embraces the '[' character with square brackets, effectively escaping it.

这解释了“奇怪的”字符串的含义，它只是包含了带有方括号的“[”字符，有效地避开了它。

My use case was to specify the name of a stored procedure with underscores in it as a filter criteria for the Profiler. So I've put string '%name[_]of[_]a[_]stored[_]procedure%' in a TextData LIKE field and it gave me trace results I wanted to achieve.

我的用例是指定存储过程的名称，并将其作为分析器的筛选标准。因此，我将字符串'%name[_]of[_][_][_][_]在TextData LIKE字段中存储[_]%，它给了我想要实现的跟踪结果。

Here is a good example from the documentation: LIKE (Transact-SQL) - Using Wildcard Characters As Literals

这里有一个来自文档的很好的例子:LIKE (Transact-SQL) -使用通配符作为文字。

#8

According to documentation:

根据文档:

You can use the wildcard pattern matching characters as literal characters. To use a wildcard character as a literal character, enclose the wildcard character in brackets.

您可以使用通配符模式匹配字符作为文字字符。若要使用通配符作为文字字符，请将通配符括在括号中。

You need to escape these three characters %_[:

您需要转义这三个字符%_[:

'5%'      LIKE '5[%]'      -- true
'5$'      LIKE '5[%]'      -- false
'foo_bar' LIKE 'foo[_]bar' -- true
'foo$bar' LIKE 'foo[_]bar' -- false
'foo[bar' LIKE 'foo[[]bar' -- true
'foo]bar' LIKE 'foo]bar'   -- true

#1

228

LIKE 'WC[[]R]S123456'

或

LIKE 'WC\[R]S123456' ESCAPE '\'