Mysql: How to filter data by using like statement in subquery-CodePudding

I've two tables as below, I wanted to filter the values where a word contains in bad_word table

Bad word Table

CREATE TABLE `bad_word` (
  `id` int(11) NOT NULL,
  `value` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8


 id value
 1  apple

Item table

CREATE TABLE `items` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

id  name
1   buy this apple
2   buy this orrange
3   buy this mango

I want to get final result after the filter

id  name
2   buy this orrange
3   buy this mango

Sample query I've tried

select * 
FROM items 
where
items.name not like CONCAT('%', select value from bad_words , '%')

CodePudding user response：

You could use an anti-join approach:

SELECT i.*
FROM items i
LEFT JOIN bad_words bw
    ON i.name LIKE CONCAT('%', bw.value, '%')
WHERE bw.value IS NULL;

Actually, it would be better to use REGEXP here with word boundaries around the bad keywords:

SELECT i.*
FROM items i
LEFT JOIN bad_words bw
    ON i.name REGEXP CONCAT('\\b', bw.value, '\\b')
WHERE bw.value IS NULL;