SQL Values splitted by ","

Question

I have a mysql database which has fields that could contain more values separated by a "," (comma)

When I extract them, I need them separately.

For example, in the db's field the values are: 1,2,3. I need them like this: 1 then 2 then 3

Can someone help me?

What do you mean `you need them`? Can you tell us what you're doing, how the queries are to be run and how they are to be consumed. Please remember we know nothing about your system. — Preet Sangha, Nov 26 '13 at 23:57
You want them as separate columns in the result set or as separate rows in a result set? — Mike Brant, Nov 26 '13 at 23:57
provide some test data, do all of them have exactly 3 values or do some have for example `1,2` and others have `1,2,3,4,5` — Tin Tran, Nov 27 '13 at 00:18
http://stackoverflow.com/questions/1096679/can-mysql-split-a-column — Tin Tran, Nov 27 '13 at 00:23

score 0 · Answer 1 · answered Nov 27 '13 at 00:12

MySql doesn't provide any string split functionality (that I'm aware of). Typically, it is better to store this type of data in separate columns anyway.

But if you don't have the capability to store it differently, you might be able to achieve some results using the substring_index function..

SELECT `value` , 
SUBSTRING_INDEX( `value` , ',', 1 ) AS a,
SUBSTRING_INDEX(SUBSTRING_INDEX( `value` , ',', 2 ),',',-1) AS b, 
SUBSTRING_INDEX(SUBSTRING_INDEX( `value` , ',', -2 ),',',1) AS c,
SUBSTRING_INDEX( `value` , ',', -1 ) AS d
FROM (SELECT "1,2,3,4" AS `value`) AS t;

This gives the following result as a row: '1,2,3,4', '1', '2', '3', '4'

See comments located here: http://dev.mysql.com/doc/refman/5.1/en/string-functions.html

As far as splitting it into separate rows, I'm not sure how you would go about that.. If it is possible to normalize your db structure that might be for the best.

to have them in different rows you can try using a bunch of `UNION` — Tin Tran, Nov 27 '13 at 00:20

score 0 · Accepted Answer · answered Nov 27 '13 at 00:37

using a defined function found here: http://blog.fedecarg.com/2009/02/22/mysql-split-string-function/ if you know your data has maximum of 3 values like 1,2,3 you can try this sqlFiddle example

SELECT value
FROM
  (
   SELECT SPLIT_STR(value,',',1) AS value FROM t
   UNION ALL
   SELECT SPLIT_STR(value,',',2) AS value FROM t
   UNION ALL
   SELECT SPLIT_STR(value,',',3) AS value FROM t
  )T1
WHERE value <> '';

if you know your data has maximum of 5 values like 1,2,3,4,5 you can just add 2 more UNION ALL...

also look at this answer http://stackoverflow.com/questions/17308669/reverse-group-concat-in-mysql you might be able to use that stored procedure. — Tin Tran, Nov 27 '13 at 00:57

SQL Values splitted by ","

2 Answers2