Opposite of MySQL FIND_IN_SET

Question

How can I do the equivalent of:

!FIND_IN_SET('needle', haystack)

Solution 1

FIND_IN_SET returns the index of the match if it is found, and returns 0 if it is not found. Since 0 is FALSE you can just use NOT FIND_IN_SET('needle', 'haystack')

Solution 2

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set

FIND_IN_SET('needle', haystack) = 0 should do the trick.

Solution 3

SELECT id FROM table where !FIND_IN_SET(needle,haystack).......

Its working for me...

Solution 4

It seems like it doesn't work if the field is NULL and therefore doesn't contain the value.

A workaround:

WHERE id NOT IN (SELECT id FROM table WHERE FIND_IN_SET(needle,haystack))

Hope it'll help!

View more solutions

Comments

[Nicola Cossu]

I agree with you but even his solution with ! instead of not does the trick. So I don't understand what's his problem :)

[Jeff Wilbert]

A note to anyone who may come across this question/solution, the use of NOT FIND_IN_SET(...) will only work correctly if your needle and haystack don't contain NULL values. It's a case where logically reading the query it sounds correct, but following the return values will find it doesn't work since the function returns NULL if either param values contain a NULL value. The proper way would be FIND_IN_SET(...) IS NULL to check for NULL BUT if your haystack could contain both NULL and and empty string ''; then just do NOT FIND_IN_SET(needle, IFNULL(haystack, '').