Re: HELP-Extacting Comma seprated String

[Felix Geerinckx]

Your explanation is not very clear.
I had the following laying around though:

USE test;
DROP TABLE IF EXISTS foo, bar, numbers;

CREATE TABLE foo (id INT UNSIGNED NOT NULL PRIMARY KEY, word CHAR(10) NOT NULL);
INSERT INTO foo (id, word) VALUES (1, 'one'), (2, 'two'), (3, 'three'), (4, 'four'), (5, 'five');

CREATE TABLE bar (id INT UNSIGNED NOT NULL PRIMARY KEY, csv CHAR(20) NOT NULL);
INSERT INTO bar (id, csv) VALUES (1, '1,3,5');

CREATE TABLE numbers (i INT UNSIGNED NOT NULL PRIMARY KEY);
INSERT INTO numbers (i) VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);

SELECT
    foo.word
FROM foo
WHERE
    foo.id IN (
        SELECT 
            SUBSTRING_INDEX(SUBSTRING_INDEX(bar.csv, ',', numbers.i), ',', -1)
        FROM bar
        JOIN numbers ON numbers.i <= CHAR_LENGTH(bar.csv) - CHAR_LENGTH(REPLACE(bar.csv, ',', '')) + 1
        WHERE
            bar.id = 1
    );

--
felix
Please use BBCode to format your messages in this forum.