我们可以在sql中使用“不喜欢”吗?

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%”而不是“%abc%”吗?我试过了，但结果好像不太对!

Is there anything similar to regex in SQL.

SQL中是否有类似regex的内容。

Eg:

例如:

i hae a table with 3 field.

我有一张有三个字段的表格。

id  name  address
1    xyz    1234 abcd 
2    abc    nomans land
3    omg    #123 new-york
3    nom    $123 &7up

can i fetch the address **with special characters with out checking each special character one by one . How

我可以取有特殊字符的地址** *，并逐一检查每个特殊字符。如何

4 个解决方案

#1

Sure, look here. Also NOT LIKE is supported.

当然,看这里。也不喜欢被支持。

#2

In SQL Server. if you wanted addresses that contained characters other than alphanumerics and spaces:

在SQL服务器。如果您想要包含字母数字和空格以外的字符的地址:

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中是否可能出现非常类似的情况。

#3

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”开头的所有订单。

#4

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 regex inquiry is apparently already answered and accepted, so is ignored in this response:

对于主题查询，没有给出“尝试并得到一些结果，但看起来不像正确的!!”的描述，但是在查看给定的数据和OP中的两个谓词时，请考虑以下内容;注意到regex的二次查询显然已经得到答复并被接受，因此在此答复中被忽略:

with
  xmp (id, name, address) as
( values ( 1  ,  'xyz'  ,  '1234 abcd '    )
       , ( 2  ,  'abc'  ,  'nomans land'   )
       , ( 3  ,  'omg'  ,  '#123 new-york' )
       , ( 3  ,  'nom'  ,  '$123 &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)};包括除第一行以外的所有内容。将谓词从不像'%abc%'这样的地址更改为'%abc%'这样的地址，将产生集合{(1)};例如，只包括第一行。任何一种形式的谓词的规范都不像“%abc%”或不像“%abc%”(像“%abc%”)那样产生相同的结果;它们在逻辑上是相同的请求。

#1