Creating a MySQL SET's from a string

Question

Is there a way to create a set from a string of separated values in MySQL? For example:

'the,quick,brown,fox' => 'the','quick','brown','fox'

A sort of inverse EXPORT_SET without the bit fiddeling.

Regards

Answer 1

You could split the text into separate elements, read into a temp table, and then select the result.

e.g.

http://forums.mysql.com/read.php?60,78776,242420#msg-242420

Answer 2

If you're trying to use the set in an IN statement, instead of splitting the string, you could do a comparison like:

SELECT * FROM `table` WHERE 'the,quick,brown,fox' REGEXP CONCAT('(^|,)','word','(,|$)');

I'm not sure how efficient this would be if your dataset is large, but it might be faster than reading into and selecting from a temporary table.

Answer 3

Tested on MySQL 5.1.41:

DROP TABLE IF EXISTS n;
CREATE TEMPORARY TABLE n AS
  SELECT -1 AS N UNION
  SELECT -2 UNION
  SELECT -3 UNION
  SELECT -4 UNION
  SELECT -5;

DROP TABLE IF EXISTS foo;
CREATE TABLE foo AS
  SELECT 'the,quick,brown,fox' AS csv;

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(foo.csv, ',', n.n), ',', 1) AS word
FROM foo JOIN n
  ON (LENGTH(REPLACE(foo.csv, ',', ''))-LENGTH(foo.csv) <= n.n+1);

Result:

+-------+
| word  |
+-------+
| fox   |
| brown |
| quick |
| the   |
+-------+