How to select from MySQL where a SET does not equal a value?

we have an issue because this is how the table was setup. How do we select from a table where a set does not contain a value? If we have a set with one,two and we do

SELECT *
FROM table
WHERE column NOT IN('one')

we will still get that row. What is the co开发者_StackOverflowrrect syntax for this?

Thanks

this is what worked:

SELECT *, FIND_IN_SET('hidden', props) As hidden
FROM gt_content
HAVING hidden IS NULL

Try:

SELECT *
FROM table
WHERE FIND_IN_SET('one',column) = 0

Some test I did:

mysql> CREATE TABLE setTest (s SET('a','b','c','d'));
Query OK, 0 rows affected (0.60 sec)

mysql> INSERT INTO setTest VALUES ('a'),('a,b'),('b'),('a,c'),('c,d');
Query OK, 5 rows affected (0.14 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM setTest;
+------+
| s    |
+------+
| a    |
| a,b  |
| b    |
| a,c  |
| c,d  |
+------+
5 rows in set (0.20 sec)

mysql> SELECT * FROM setTest WHERE FIND_IN_SET('a',s) = 0;
+------+
| s    |
+------+
| b    |
| c,d  |
+------+
2 rows in set (0.12 sec)

mysql> SELECT * FROM setTest WHERE FIND_IN_SET('b',s) = 0;
+------+
| s    |
+------+
| a    |
| a,c  |
| c,d  |
+------+
3 rows in set (0.00 sec)


mysql> SELECT * FROM setTest WHERE FIND_IN_SET('b',s) > 0;
+------+
| s    |
+------+
| a,b  |
| b    |
+------+
2 rows in set (0.04 sec)

From the mysql manual at http://dev.mysql.com/doc/refman/5.0/en/set.html:

Normally, you search for SET values using the FIND_IN_SET() function or the LIKE operator:

  SELECT * FROM tbl_name WHERE FIND_IN_SET('value',set_col)>0;
  SELECT * FROM tbl_name WHERE set_col LIKE '%value%';

The first statement finds rows where set_col contains the value set member. The second is similar, but not the same: It finds rows where set_col contains value anywhere, even as a substring of another set member.