mysql 获取子串,使用分隔符提取MySQL子串

I want to extract the substrings from a string in MySQL. The string contains multiple 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 the colors field and extract the substrings as red, yellow and green as separated by comma.

解决方案

Unfortunately, MySQL does not feature a split string function.

As in the link above indicates there are User-defined Split function.

A more verbose version to fetch the data can be the following:

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;