如何排除来自mysql查询的结果列表?

时间:2022-10-13 13:05:04

Lets say I want to select all rows that does not contain a long list of words. Lets say this list is shortened for the sake of this example to only "example1" and "example2".

假设我要选择不包含长单词列表的所有行。我们说这个列表是为了这个例子而缩短的,只为了“example1”和“example2”。

Im guessing using REGEXP would be the best option, instead of specifying

我猜测使用REGEXP将是最好的选择,而不是指定。

and field not like '%example1%' and field not like '%example2%'

而字段不像'%example1%'和字段不像'%example2%'

but Im not sure how to go about it. Im guessing something like this?

但是我不知道该怎么做。我猜是这样的吗?

WHERE !REGEXP(field, '/example1|example2/')

2 个解决方案

#1


1  

I would use LOCATE:

我将使用定位:

set @string = '/example1|example2/';
select * from YourTable
where locate(YourStringField,@String) = 0

http://www.sqlfiddle.com/#!2/6d075/3

http://www.sqlfiddle.com/ ! 2/6d075/3

#2


1  

MySQL support for regular expressions is very limited, but I think you could use this solution:

MySQL对正则表达式的支持非常有限,但是我认为您可以使用这个解决方案:

SELECT *
FROM yourtable
WHERE
  field NOT RLIKE '[[:<:]]example1[[:>:]]|[[:<:]]example2[[:>:]]'

where [[:<:]] and [[:>:]] are word boundaries, and | is the OR operator

where[[:]]和[[:>:]]是单词边界,|是OR运算符?

#1


1  

I would use LOCATE:

我将使用定位:

set @string = '/example1|example2/';
select * from YourTable
where locate(YourStringField,@String) = 0

http://www.sqlfiddle.com/#!2/6d075/3

http://www.sqlfiddle.com/ ! 2/6d075/3

#2


1  

MySQL support for regular expressions is very limited, but I think you could use this solution:

MySQL对正则表达式的支持非常有限,但是我认为您可以使用这个解决方案:

SELECT *
FROM yourtable
WHERE
  field NOT RLIKE '[[:<:]]example1[[:>:]]|[[:<:]]example2[[:>:]]'

where [[:<:]] and [[:>:]] are word boundaries, and | is the OR operator

where[[:]]和[[:>:]]是单词边界,|是OR运算符?