Hello,
Here is a set based solution.
Using v4.0.6 gamma on w2k pro.
CREATE TABLE broad (name varchar(10) not null,
groups varchar(20) not null,
primary key (name));
INSERT INTO broad values("DOG","1,3,4,2"),
("CAT","2,4,5"),
("BIRD","3,1,2"),
("SHEEP","3,9,11,1,17,2");
-- Create table of digits from 1-N where N is >= to the length of
-- largest groups string (number of characters in string).
CREATE TABLE digits
(digit tinyint unsigned not null primary key);
INSERT INTO digits
values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15);
-- Parse out each number in the groups string into column number.
-- @s holds the delimiter used in the string (groups) to be parsed (ie.
comma).
SET @s:=',';
SELECT name,
1*(LTRIM(RTRIM(SUBSTRING(CONCAT(@s,groups,@s),digit+1,
LOCATE(@s,CONCAT(@s,groups,@s),digit+1)-
(LOCATE(@s,CONCAT(@s,groups,@s),digit)+1))))) as number
from broad,digits
where digit between LOCATE(@s,CONCAT(@s,groups,@s),digit) and
LENGTH(CONCAT(@s ,groups)) -1
-- ORDER BY name,number
ORDER BY number,name;
MS Server guy very interested in MySql.
www.rac4sql.net
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
