Creating MySQL SET from a string

Question

Is there a way to create a set from a string of split values ​​in MySQL? For instance:

'fast, brown, fox' => 'That', 'fast', 'brown', 'fox'

View inverse EXPORT_SET without bit distortion.

Hi

Answer 1

If you are trying to use a set in an IN statement, instead of splitting a string, you can do a comparison, for example:

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

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

Answer 2

Tested in 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, ',', nn), ',', 1) AS word FROM foo JOIN n ON (LENGTH(REPLACE(foo.csv, ',', ''))-LENGTH(foo.csv) <= n.n+1); 

Result:

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

Answer 3

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

eg.

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

Answer 4

Will FIND_IN_SET not solve your problem?

FIND_IN_SET ()