SQL Update - String Splitting

I have a problem about SQL. How can i split a string with a delimiter ' ' character and access t开发者_开发百科hat parts to update a row ? .. To clearify that :

Table is like:

Products ( Name, Colors,         Data1, Data2, Data3)
              a  red green blue
              b  white black

And i want to do something like :

Products ( Name, Colors,         Data1, Data2, Data3)
              a  red green blue    red  green  blue
              b  white black     white  black

---

Looks like the numbers of columns(Data1, Data2, Data3) is not dynamic.

So I assume that the field 'Colors' can have maximum of three words separated by space.

Then the quick and dirty way to do is write a series of update statements like this:

UPDATE Products SET Data1 = get_color(Colors, ' ', 1);

UPDATE Products SET Data2 = get_color(Colors, ' ', 2);

UPDATE Products SET Data3 = get_color(Colors, ' ', 3);

where get_color is going to be a custom function that returns the nth word from a given string:

CREATE FUNCTION get_color(
x VARCHAR(255),
delim VARCHAR(12),
pos INT
)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
delim, '');

---

The sensible solution is to create at least one more table:

product
=======

product_id (PK)
product_name

color
=====

color_id (PK)
product_id (FK to product.product_id)
color_name