CODEWITHSUNDEEP
mysqlsqldatabaseset
phirschybar
phirschybar

Reputation: 8579

Opposite of MySQL FIND_IN_SET

How can I do the equivalent of:

!FIND_IN_SET('needle', haystack)

Upvotes: 25

Views: 41019

Answers (6)

Karo
Karo

Reputation: 11

Equals to 0 doesn't work, you have to use null.

SELECT id FROM table WHERE FIND_IN_SET(needle,haystack) IS NULL

Upvotes: 1

Hussain
Hussain

Reputation: 138

.andWhere('(NOT FIND_IN_SET(:userID, notification.deletedBy) OR notification.deletedBy IS NULL)',{userID:userId})

Upvotes: 0

Paul
Paul

Reputation: 141827

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 use NOT FIND_IN_SET('needle', haystack)

Upvotes: 60

Guillaume Renoult
Guillaume Renoult

Reputation: 966

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!

Upvotes: 4

Sandeep Jadhav
Sandeep Jadhav

Reputation: 61

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

Its working for me...

Upvotes: 6

Pelshoff
Pelshoff

Reputation: 1464

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.

Upvotes: 8

Related Questions