I have a table in Sql Server 2000 called Table1with a few columns, one of the columns is varchar(255), called 'Col5'.
我在Sql Server 2000中有一个名为Table1的表,有几列,其中一列是varchar(255),称为'Col5'。
The contents if one of the rows in this column is [test].
如果此列中的一行是[test],则为内容。
When I try to test for a part of this string using the LIKE operator, I cannot search for the [ in any way.
当我尝试使用LIKE运算符测试此字符串的一部分时,我无法以任何方式搜索[。
This is what I get:
这就是我得到的:
1- trying SELECT * FROM Table1 WHERE Col1 LIKE '%[%' returns NO results.
1-尝试SELECT * FROM Table1 WHERE Col1 LIKE'%[%'返回NO结果。
2- trying SELECT * FROM Table1 WHERE Col1 LIKE '%[t%' returns NO results.
2-尝试SELECT * FROM Table1 WHERE Col1 LIKE'%[t%'返回NO结果。
3- trying SELECT * FROM Table1 WHERE Col1 LIKE '%t]%' returns 1 results which as expected.
3-尝试SELECT * FROM Table1 WHERE Col1 LIKE'%t]%'返回1个结果,如预期的那样。
4- trying SELECT * FROM Table1 WHERE Col1 LIKE '%test%' returns 1 results which as expected.
4-尝试SELECT * FROM Table1 WHERE Col1 LIKE'%test%'返回1个结果,如预期的那样。
This is a strange behaviour, is it a bug, what's going on, any ideas?
这是一个奇怪的行为,它是一个错误,发生了什么,任何想法?
1 个解决方案
#1
2
That character has special meaning in the pattern syntax and needs to be escaped either by defining an explicit escape character
该字符在模式语法中具有特殊含义,需要通过定义显式转义字符进行转义
where Col1 like '%/[%' ESCAPE '/'
or by using more square brackets
或者使用更多的方括号
where Col1 like '%[[]%'
In addition to its role in escaping characters it is used in the syntax when defining ranges or sets of characters to match. e.g. LIKE '[0-9]%' to find all values starting with a digit.
除了它在转义字符中的作用之外,它还在定义要匹配的范围或字符集时在语法中使用。例如像'[0-9]%'一样找到以数字开头的所有值。
When the range or set is not closed off with a ] it appears as though SQL Server just adds one on to the end. So c like '%[%' is treated as c like '%[%]' and finds all values containing the % character.
如果没有关闭范围或集合,则看起来好像SQL Server只是在最后添加了一个。所以c像'%[%'被视为c''%[%]'并查找包含%字符的所有值。
#1
2
That character has special meaning in the pattern syntax and needs to be escaped either by defining an explicit escape character
该字符在模式语法中具有特殊含义,需要通过定义显式转义字符进行转义
where Col1 like '%/[%' ESCAPE '/'
or by using more square brackets
或者使用更多的方括号
where Col1 like '%[[]%'
In addition to its role in escaping characters it is used in the syntax when defining ranges or sets of characters to match. e.g. LIKE '[0-9]%' to find all values starting with a digit.
除了它在转义字符中的作用之外,它还在定义要匹配的范围或字符集时在语法中使用。例如像'[0-9]%'一样找到以数字开头的所有值。
When the range or set is not closed off with a ] it appears as though SQL Server just adds one on to the end. So c like '%[%' is treated as c like '%[%]' and finds all values containing the % character.
如果没有关闭范围或集合,则看起来好像SQL Server只是在最后添加了一个。所以c像'%[%'被视为c''%[%]'并查找包含%字符的所有值。