At 16:06 -0800 3/30/05, Hassan Schroeder wrote:
Luke Bowerman wrote:
With the column:
mySetCol SET ('cat','dog','mouse','giraffe','lion')
A row with the value "dog,mouse,lion" would return 3
A row with the value "cat,giraffe" would return 2
I've been able to get the MySQL server to give me a binary version
of the data...
SELECT RPAD(BIN(mySetCol+0),5,'0') AS bin from projects;
+-------+
| bin |
+-------+
| 11111 |
| 11111 |
| 11000 |
| 10000 |
| 10110 |
+-------+
If there was some way to count the occurence of the number "1"
within the string I'd have my number but I don't know of anyway to
that within MySQL.
I believe SELECT LENGTH(REPLACE(BIN(mySetCol+0),0,'')) FROM projects
will give you what you want...
Easier to use SELECT BIT_COUNT(mySetCol+0).
With BIT_COUNT(), you may not need the +0, either.
Example:
drop table if exists t;
create table t (s set('a','b','c','d'));
insert into t set s = 'a';
insert into t set s = 'a,b';
insert into t set s = 'a,c';
insert into t set s = 'b,c,d';
insert into t set s = 'a,b,c,d';
select s, bit_count(s) from t;
Result:
+---------+--------------+
| s | bit_count(s) |
+---------+--------------+
| a | 1 |
| a,b | 2 |
| a,c | 2 |
| b,c,d | 3 |
| a,b,c,d | 4 |
+---------+--------------+
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
