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 [R] S123456。
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.
我找到了一些关于在LIKE中使用ESCAPE关键字的信息,但我不明白如何使用它将方括号视为常规字符串。
9 个解决方案
#1
253
LIKE 'WC[[]R]S123456' or
LIKE 'WC\[R]S123456' ESCAPE '\'Should work.
#2
87
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
25
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
18
Here is what I actually used:
这是我实际使用的:
like 'WC![R]S123456' ESCAPE '!'#5
9
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
2
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#7
1
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.
您也可以使用键盘上没有的特殊字符,而不是键盘上的'\'或其他字符。如果您不希望用户输入意外地用作转义字符,则根据您的使用情况,这可能是必要的。
#8
1
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.
我的用例是指定带有下划线的存储过程的名称作为Profiler的过滤条件。所以我把[_] [_] [_]存储[_]过程%'的字符串'%name [_]放在TextData LIKE字段中,它给了我想要实现的跟踪结果。
Here is a good example from the documentation:LIKE (Transact-SQL) - Using Wildcard Characters As Literals
以下是文档中的一个很好的示例:LIKE(Transact-SQL) - 使用通配符作为文字
#9
0
Use Following.
For user input to search as it is, use escape, in that it will require following replacement for all special characters (below covers all of SQL Server).
要使用户输入按原样搜索,请使用escape,因为它需要对所有特殊字符进行以下替换(下面将涵盖所有SQL Server)。
Here single quote "'" is not taken as it does not affect like clause as It is a matter of string concatenation.
这里不引用单引号“'”,因为它不会影响like子句,因为它是字符串连接的问题。
"-" & "^" & "]" replace is not required as we are escaping "[".
“ - ”&“^”&“]”不需要替换,因为我们正在逃避“[”。
String FormattedString = "UserString".Replace("ð","ðð").Replace("_", "ð_").Replace("%", "ð%").Replace("[", "ð[");Then, in SQL Query it should be as following. (In parameterised query, string can be added with patterns after above replacement).
然后,在SQL Query中它应该如下。 (在参数化查询中,字符串可以在上面的替换后添加模式)。
To search exact string.
要搜索确切的字符串。
like 'FormattedString' ESCAPE 'ð'To search start with string
要搜索以字符串开头
like '%FormattedString' ESCAPE 'ð'To search end with string
用字符串搜索结束
like 'FormattedString%' ESCAPE 'ð'To search contain with string
要搜索包含字符串
like '%FormattedString%' ESCAPE 'ð'and so on for other pattern matching. But direct user input needs to format as mentioned above.
等等其他模式匹配。但是直接用户输入需要如上所述进行格式化。
#1
253
LIKE 'WC[[]R]S123456' or
LIKE 'WC\[R]S123456' ESCAPE '\'Should work.
#2
87
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
25
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
18
Here is what I actually used:
这是我实际使用的:
like 'WC![R]S123456' ESCAPE '!'#5
9
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
2
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#7
1
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.
您也可以使用键盘上没有的特殊字符,而不是键盘上的'\'或其他字符。如果您不希望用户输入意外地用作转义字符,则根据您的使用情况,这可能是必要的。
#8
1
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.
我的用例是指定带有下划线的存储过程的名称作为Profiler的过滤条件。所以我把[_] [_] [_]存储[_]过程%'的字符串'%name [_]放在TextData LIKE字段中,它给了我想要实现的跟踪结果。
Here is a good example from the documentation:LIKE (Transact-SQL) - Using Wildcard Characters As Literals
以下是文档中的一个很好的示例:LIKE(Transact-SQL) - 使用通配符作为文字
#9
0
Use Following.
For user input to search as it is, use escape, in that it will require following replacement for all special characters (below covers all of SQL Server).
要使用户输入按原样搜索,请使用escape,因为它需要对所有特殊字符进行以下替换(下面将涵盖所有SQL Server)。
Here single quote "'" is not taken as it does not affect like clause as It is a matter of string concatenation.
这里不引用单引号“'”,因为它不会影响like子句,因为它是字符串连接的问题。
"-" & "^" & "]" replace is not required as we are escaping "[".
“ - ”&“^”&“]”不需要替换,因为我们正在逃避“[”。
String FormattedString = "UserString".Replace("ð","ðð").Replace("_", "ð_").Replace("%", "ð%").Replace("[", "ð[");Then, in SQL Query it should be as following. (In parameterised query, string can be added with patterns after above replacement).
然后,在SQL Query中它应该如下。 (在参数化查询中,字符串可以在上面的替换后添加模式)。
To search exact string.
要搜索确切的字符串。
like 'FormattedString' ESCAPE 'ð'To search start with string
要搜索以字符串开头
like '%FormattedString' ESCAPE 'ð'To search end with string
用字符串搜索结束
like 'FormattedString%' ESCAPE 'ð'To search contain with string
要搜索包含字符串
like '%FormattedString%' ESCAPE 'ð'and so on for other pattern matching. But direct user input needs to format as mentioned above.
等等其他模式匹配。但是直接用户输入需要如上所述进行格式化。