like and not like in one mysql query

Question

I want to do a query containing 'like' and 'not like'.

Current example: i want everything starting with '1|%' but not with '1|6|199|%' or '1|6|200|%'.

Current query:

'SELECT * FROM `links` WHERE `category` LIKE '1|%' NOT LIKE '1|6|199|%','1|6|200|%' ORDER BY `score` DESC LIMIT 9'.

But that doesn't work. Any tips? thx

"Does not work" should be a little more precise. I assume you get a syntax error - in which case Michael Haren has your solution. — Daniel Schneller, Jun 15 '09 at 13:12

Michael Haren · Accepted Answer · 2009-06-15T13:21:40.183

36

Just add "and category"...

SELECT * FROM links 
WHERE category LIKE '1|%' 
  AND category NOT LIKE '1|6|199|%','1|6|200|%' 
ORDER BY score DESC LIMIT 9

Actually, the comma separated condition is not a syntax I'm familiar with. If that's not working, try this instead:

SELECT * FROM links 
WHERE category LIKE '1|%' 
  AND category NOT LIKE '1|6|199|%'
  AND category NOT LIKE '1|6|200|%' 
ORDER BY score DESC LIMIT 9

edited Jun 15 '09 at 13:21

answered Jun 15 '09 at 13:00

Michael Haren

105,752
40
168
205

thx! it worked! However, what is wrong with the following? SELECT * FROM `links` WHERE `category` LIKE '1|6|%' AND `category` NOT LIKE '1|6|137|%','1|6|151|%','1|6|118|%','1|6|176|%','1|6|67|%','1|6|199|%','1|6|160|%' ORDER BY `score` DESC LIMIT 9 ? it shows empty but its not thx! – Maurice Kroon Jun 15 '09 at 13:16
if you intebd to perform several exclusion, I suggest you use SUBSTR(category, 1, 8) NOT IN ( 1|6|137|','1|6|151|', ... ). – instanceof me Jun 15 '09 at 15:55

score 2 · Answer 2 · answered Jun 15 '09 at 13:05

You can use regexps:

SELECT  *
FROM    links 
WHERE   category LIKE '1|%' 
        AND category NOT REGEXP '^1\\|6\\|(199|200)\\|'
ORDER BY
        score DESC
LIMIT 9

Note that REGEXP's don't use indexes, while LIKE does.

In this query, LIKE '1|%' will serve as a coarse filter using the index on category if any, while REGEXP's will fine filter the results.

score 2 · Answer 3 · edited Sep 16 '17 at 21:24

2

I think a bigger problem is that you have de-normalized tables. The correct answer would be to normalize your tables.

But if you can't do that, you should use commas as separators and FIND_IN_SET() instead:

WHERE FIND_IN_SET('1', category) > 1
  AND FIND_IN_SET('6', category) > 1
  AND FIND_IN_SET('199', category) = 0
  AND FIND_IN_SET('200', category) = 0

edited Sep 16 '17 at 21:24

zb226

9,586
6
49
79

answered Jun 15 '09 at 15:42

longneck

11,938
2
36
44

score 0 · Answer 4 · edited Jun 20 '20 at 09:12

It is also possible to use two inner join, probably not the best solution for this query, but could still be useful.

SELECT * FROM links

INNER JOIN (SELECT * FROM links WHERE category NOT LIKE '1|6|199|%') AS escl1 ON (links.category=escl1.category)

INNER JOIN (SELECT * FROM links WHERE category NOT LIKE '1|6|200|%') AS escl2 ON (links.category=escl2.category)

WHERE category LIKE '1|%'

ORDER BY score DESC LIMIT 9

like and not like in one mysql query

4 Answers4