我们可以在 sql 中使用“NOT LIKE”吗
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9301439/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Can we use "NOT LIKE " in sql
提问by zod
Can we use "NOT LIKE '%abc%'"just opposite of LIKE '%abc%'?
i tried and got some result but dont look like correct!!
我们可以使用"NOT LIKE '%abc%'"相反的LIKE '%abc%'吗？我尝试并得到了一些结果，但看起来不正确！！
Is there anything similar to regex in SQL.
有没有类似的regex in SQL。
Eg:
例如：
i hae a table with 3 field.
我有一张有 3 个字段的表。
id name address
1 xyz 1234 abcd
2 abc nomans land
3 omg #123 new-york
3 nom 3 &7up
can i fetch the address **with special characters with out checking each specialcharacter one by one . How
我可以获取带有特殊字符的地址** 而不用逐个检查每个特殊字符吗？如何
回答by onedaywhen
In SQL Server. if you wanted addresses that contained characters other than alphanumerics and spaces:
在 SQL Server 中。如果您想要包含字母数字和空格以外的字符的地址：
address LIKE '%[^0-9a-zA-Z ]%';
noting the ^character means "any single character not within the specified range". Not sure if something very similar is possible in DB2.
注意到该^字符的意思是“不在指定范围内的任何单个字符”。不确定在 DB2 中是否可能有非常相似的东西。
回答by Lonnie Best
In db2 (version 9.7.900.250), I've successfully specified "not like" this way:
在 db2（版本 9.7.900.250）中，我已经成功地以这种方式指定了“不喜欢”：
select * from orders
where not(orders.order_number like 'S%')
This shows all orders where the order# does NOT start with a capital "S".
这显示了所有订单号不以大写“S”开头的订单。
回答by CRPence
No description was given for what was "tried and got some result but don't look like correct!!" with regard to the Subject inquiry, but in review of the given data and the two predicates from the OP, consider the following; noting, the secondary regexinquiry is apparently already answered and accepted, so is ignored in this response:
没有给出“尝试并得到一些结果但看起来不正确！！”的描述。关于主题查询，但在给定数据和来自 OP 的两个谓词时，请考虑以下事项；请注意，次要正则表达式查询显然已经得到回答和接受，因此在此响应中被忽略：
with
xmp (id, name, address) as
( values ( 1 , 'xyz' , '1234 abcd ' )
, ( 2 , 'abc' , 'nomans land' )
, ( 3 , 'omg' , '#123 new-york' )
, ( 3 , 'nom' , '3 &7up' )
)
select id
from xmp
where address NOT LIKE '%abc%'
The above DB2 query should yield the set {(2), (3), (3)}; i.e. include all but the first row. Changing the predicate from address NOT LIKE '%abc%'to address LIKE '%abc%'should yield the set {(1)}; i.e. include only the first row. The specification of the predicate in either form address NOT LIKE '%abc%'or NOT (address LIKE '%abc%')should yield the same result; they are logically identical requests.
上面的 DB2 查询应该产生集合 {(2), (3), (3)}; 即包括除第一行之外的所有内容。将谓词从address NOT LIKE '%abc%'to更改为address LIKE '%abc%'应该产生集合 {(1)}; 即只包括第一行。谓词的指定形式address NOT LIKE '%abc%'或NOT (address LIKE '%abc%')应产生相同的结果；它们是逻辑上相同的请求。
