Mysql substring extraction using delimiter

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.

Answers


A possible duplicate of this: Split value from one field to two

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;

Check the use of SPLIT_STR function here at line 64 to 69


Need Your Help

A keyboard shortcut to comment/uncomment the select text in Android Studio

android-studio

How do I comment out several lines of text selected in the IDE with a control key combination?

JFormattedTextField with MaskFormatter

java swing datetime textfield

I have a JFormattedTextField that I use to restrict entries of a date and time. I want to use a MaskFormatter though to show the placeholder chars. Is there a way to use a MaskFormatter on top of the