Mysql substring calculation using delimiter

Question

I want to extract substrings from a string in mysql. The string contains several substrings separated by commas (','). I need to extract these substrings using any mysql functions.

For example:

Table Name: Product
-----------------------------------
item_code  name    colors
-----------------------------------
102        ball     red,yellow,green
104        balloon  yellow,orange,red  

I want to select a color field and extract the substrings as red, yellow and green, separated by a comma.

Answer 1

Possible duplicate: Divide the value from one field into two

Unfortunately, MySQL does not have a line-breaking function. As indicated in the link above, there is a custom split function .

A more detailed version for retrieving data may be as follows:

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(colors, ',', 1), ',', -1) as colorfirst,
       SUBSTRING_INDEX(SUBSTRING_INDEX(colors, ',', 2), ',', -1) as colorsecond
....
       SUBSTRING_INDEX(SUBSTRING_INDEX(colors, ',', n), ',', -1) as colornth
  FROM product;

Answer 2

SPLIT_STR 64 69