MySQL Bugs: #2501: Comfortable work with sets

Bug #2501	Comfortable work with sets
Submitted:	24 Jan 2004 13:41	Modified:	25 Jan 2004 12:26
Reporter:	Alexander	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:	4.15	OS:	Linux (Linux Mandrake 9)
Assigned to:	Peter Gulutzan	CPU Architecture:	Any

Description:
I can't believe that there's no function which would do for sets something like that:
SELECT set_col FROM set_table WHERE CONTAINS(set_col, 'val3, val1, val5');
which would work NOT with bits, but normally, with strings and which wouldn't care the order of enumeration. Is there...? Don't disappoint me, guyz! :)

How to repeat:
Nothing to repeat.

Suggested fix:
Maybe such function should be added? :)

PeterG Please comment this.
Thanks in advance.

As with any other data type, one can say: 
WHERE set_column IN ('val1','val2','val3');

By the way: 
 
The reply here assumes that you have rows containing 'val1' etc. and your CONTAINS 
statement is an attempt to say = 'val1' OR = 'val2' etc. If there has been a 
mistunderstanding, please accept my apologies and send us an example of what you 
want.

Hi, Peter,

no, I mean not that. I mean something like SELECT ... WHERE ('val3,val1,val2') IN set_column, if to say with your words :)

In other words, if for example I have the following set type entries:

jane,fred,marcia
jane,marcia

with this non-existent function CONTAINS I want to find entries, that contain subset of elements given as enumeration. I.e. CONTAINS(set_column,'marcia,jane') would return me these both entries, coz every of them contain 'marcia' and 'jane' strings...

So far, if I got it right, the only way to do such thing is:

SELECT * FROM e_table WHERE e LIKE '%marcia%' or '%jane%'; (more

If so, is it a good way, is it fast enough? Or some optimized function for working with SETs would beat this?

Okay. You want to treat column e as if it's a number, and use MySQL's boolean 
(AND,OR,NOT) operators with it. To do so, check what the numeric values are 
by looking at their enumeration order, for example with 'jane,fred,marcia' jane=1, 
fred=2, marcia=3. For example: 
 
CREATE TABLE e_table (e SET('jane','fred','marcia')); /* jane=1, fred=2, marcia=3 */ 
INSERT INTO e_table VALUES ('jane,fred');                /* row #1 */ 
INSERT INTO e_table VALUES ('jane,fred,marcia');    /* row #2 */ 
INSERT INTO e_table VALUES ('fred');                       /* row #3 */ 
SELECT e,CAST(e AS UNSIGNED) FROM e_table;  /* to see values in numeric way */ 
SELECT * FROM e_table WHERE (e & (1|2)) = (1|2);  /* this gets row #1 and row #2 */ 
  
There is no need for a new non-standard function, then.