MySQL Exclude Over Include in FIND_IN_SET()

Question

For example, I have a table like this:

----------------------------------------------------------------------------
|   id     |    title     |                        tags                    |
----------------------------------------------------------------------------
|    1     | Title 1      |  funny, adventure                              |
----------------------------------------------------------------------------
|    2     | Title Two    |  funny, short, horror                          |
----------------------------------------------------------------------------
|    3     | Title III    |  funny, short, adventure                       |
----------------------------------------------------------------------------

How can I query that I want to include funny or short but exclude horror in the results? Like the exclude will overpower even if one of its include criteria matches?

----------------------------------------------------------------------------
|   id     |    title     |                        tags                    |
----------------------------------------------------------------------------
|    1     | Title 1      |  funny, adventure                              |
----------------------------------------------------------------------------
|    3     | Title III    |  funny, short, adventure                       |
----------------------------------------------------------------------------

score 1 · Answer 1 · answered Feb 23 '18 at 03:29

1

Try this -

SELECT * FROM table_name WHERE NOT find_in_set('horror',tags) <> 0

answered Feb 23 '18 at 03:29

phpforcoders

326
1
9

It will still return the records with horror tags. – James Arnold Feb 23 '18 at 03:45
Remove spaces from comma separated values then run query with find_in_set – phpforcoders Feb 23 '18 at 03:56
1

You can also try this SELECT * FROM `table_name` WHERE tags NOT LIKE "%horror%" – phpforcoders Feb 23 '18 at 03:57
Thanks Saurabh. Yes that works now. Also working is using the REGEXP. I decided to use the REGEXP because of performance issues of LIKE clauses. SELECT * FROM table_name WHERE tags NOT REGEXP 'horror'; – James Arnold Feb 23 '18 at 05:56
Read out https://stackoverflow.com/questions/16646686/mysql-regexp-vs-like – phpforcoders Feb 23 '18 at 06:05

MySQL Exclude Over Include in FIND_IN_SET()

1 Answers1