MySQL NOT LIKE sentence returning "like" results

Question

I've been trying to figure this out, but no success yet. I have two tables, each one with a single varchar column:

Table 1: srch(word varchar)
Table 2: tags (tag varchar)

The values in each table are:

srch: "joining", "tables", "snakes"
tags: "join", "table", "group"

What I'm trying to achieve is get the words from search (srch) table that don't match with tags table, however a need to do it using wildcard. So, when I do it with LIKE, it works alright:

select s.word from srch s, tags t where s.word like concat(t.tag,"%") group by s.word

I need to get the words that don't match with tags table, in this case the word 'snake', so, if I try with NOT LIKE it doesn't work:

select s.word from srch s, tags t where s.word not like concat(t.tag,"%") group by s.word

With this sentence, it returns all the values from srch table.

EDIT: Sample code:

CREATE TABLE `srch` (
  `word` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `srch` VALUES ('joining'),('tables'),('snakes');

CREATE TABLE `tags` (
  `tag` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `tags` VALUES ('join'),('table'),('group');

Any hint for this query? Thank you.

Each word from srch table is NOT LIKE at least 1 word in the tags table, therefore it is returned — JScoobyCed, May 14 '20 at 17:03

Jason Groulx · Answer 1 · 2020-05-14T17:20:36.103

0

You could accomplish this by using WHERE NOT REGEXP in combination with a GROUP_CONCAT to make up a string of all the tags you want to filter out.

SELECT word 
FROM srch
WHERE word NOT REGEXP (SELECT GROUP_CONCAT(tag SEPARATOR '|') FROM tags)

edited May 14 '20 at 17:20

answered May 14 '20 at 17:06

Jason Groulx

400
2
10

Hi, thank you, I tried what you posted, but it returns no results. – Andino May 14 '20 at 17:11
That's strange, I just ran your create tables statements and insert statements and was able to get the word snakes to return. – Jason Groulx May 14 '20 at 17:13
@Andino Have a look http://imgur.com/a/1V9NdDV – Jason Groulx May 14 '20 at 17:16

score 0 · Accepted Answer · answered May 14 '20 at 17:42

0

Do not do a join; instead do a subquery select and use the "NOT EXISTS" check:

SELECT s.word
FROM srch s
WHERE NOT EXISTS (
  SELECT tag
  FROM tags t 
  WHERE s.word LIKE CONCAT(t.tag,'%')
)

Result:

// snakes

answered May 14 '20 at 17:42

Martin

22,212
11
70
132

Thank you Martin, this is what I needed, I appreciate it. – Andino May 14 '20 at 18:00

MySQL NOT LIKE sentence returning "like" results

2 Answers2