0

I have a select statement which fetches a value field on an ID

177,175,173,178,179

What is returned is anywhere from null, a single entry, to multiple comma delimited values in this field.

How can I use SQL to return 1 row for each value based on this comma delimited field?

axel22
  • 32,045
  • 9
  • 125
  • 137
mprototype
  • 283
  • 1
  • 2
  • 13
  • 1
    possible duplicate of [Can you split/explode a field in a MySQL query?](http://stackoverflow.com/questions/471914/can-you-split-explode-a-field-in-a-mysql-query) – Paul Draper Dec 16 '13 at 04:30
  • do you mean that you want multiple row values to be concated with comma delimiter and you want all together under a single row......... – Nidhish Krishnan Dec 16 '13 at 04:32
  • Don't store data like this. Consider normalizing the table and keeping every number in a separate row. – user4035 Dec 16 '13 at 04:35

1 Answers1

1

As it has already been mentioned in comments consider to normalize your data to be able to normally maintain and query your data.

In the meantime you achieve your goal in pure SQL with the help of tally(number) table which you can create like this

CREATE TABLE tally (n INT NOT NULL PRIMARY KEY);
INSERT INTO tally
SELECT a.N + b.N * 10 + 1 n
  FROM 
(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
ORDER BY n;

Now your might look like

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(value, ',', n), ',', -1) value
  FROM table1 CROSS JOIN tally
 WHERE id = 1
   AND n <= 1 + (LENGTH(value) - LENGTH(REPLACE(value, ',', '')))

Output:

| VALUE |
|-------|
|   177 |
|   175 |
|   173 |
|   178 |
|   179 |

Here is SQLFiddle demo

peterm
  • 91,357
  • 15
  • 148
  • 157