sql query Split Color

Question

Possible Duplicate:
Can Mysql Split a column?
Mysql string split

I have this in a table:

id  color
1   red/green/blue/orange
2   blue
3   
4   red/green

I will a sql query maybe this :

color
red
green
blue
orange
blue
red
green

normalize your table, and this problem will go away automatically. — Marc B, Oct 31 '12 at 15:03
No, *please* don't do that. Every time you design a table like this a kitten somewhere dies. I recommend you read here: http://en.wikipedia.org/wiki/Database_normalization — Mark Byers, Oct 31 '12 at 15:03

score 0 · Answer 1 · answered Oct 31 '12 at 15:18

0

What the commenters want to suggest, is splitting the values into separate rows:

id color
1  red
1  green
1  blue
1  orange
2  blue
3  NULL
4  red
4  green

Then you can easily select:

select color from color_table;

or every color only once:

select distinct color from color_table;

answered Oct 31 '12 at 15:18

Olaf Dietsche

You're right, but suggesting a *full* normalization might be too much for the first step. – Olaf Dietsche Oct 31 '12 at 15:20

1 Answers1