36

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.

informatik01
  • 16,038
  • 10
  • 74
  • 104
Bibin Jose
  • 563
  • 4
  • 11
  • 19
  • item_code,name and colors are the fields of table Product and the values are item_code name colors 102 ball red,green,blue – Bibin Jose Jan 25 '16 at 12:25

3 Answers3

68

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;
Community
  • 1
  • 1
Sameer Mirji
  • 2,135
  • 16
  • 28
  • 2
    If the string may contains multiple number of substrings what we will do in generic code instead of fetching each one by one? there is no fixed number of substrings, it may vary – Bibin Jose Jan 25 '16 at 13:02
  • 2
    In such a case, you might have to create a Stored Procedure and use `for` loop to iterate through the length of string tokenized by `','` – Sameer Mirji Jan 25 '16 at 15:24
  • 1
    The _above code_ **works for me** when I use it in a stored procedure using a loop. – Bibin Jose Jan 28 '16 at 07:30
  • 3
    @BibinJose why don't you post the code that works for you here so that you can help us... I am not able to find out how you can use loop to get this. – aidonsnous Sep 11 '17 at 03:46
  • 2
    I think I found a small problem. If the colors column contains just two colors – "red,yellow" for example, instead of "red,yellow,green" – then the query result will be red, yellow and yellow... And if the colors column contains just "red" then the result will be red, red and red. – Paul Chris Jones Dec 20 '18 at 08:55
4

Based on https://blog.fedecarg.com/2009/02/22/mysql-split-string-function/, here is a way to access a value from a delimiter separated array:

/*
  usage:
    SELECT get_from_delimiter_split_string('1,5,3,7,4', ',',  1); -- returns '5'
    SELECT get_from_delimiter_split_string('1,5,3,7,4', ',',  10); -- returns ''
*/
CREATE FUNCTION get_from_delimiter_split_string(
  in_array varchar(255),
  in_delimiter char(1),
  in_index int
)
RETURNS varchar(255) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci
RETURN REPLACE( -- remove the delimiters after doing the following:
  SUBSTRING( -- pick the string
    SUBSTRING_INDEX(in_array, in_delimiter, in_index + 1), -- from the string up to index+1 counts of the delimiter
    LENGTH(
      SUBSTRING_INDEX(in_array, in_delimiter, in_index) -- keeping only everything after index counts of the delimiter
    ) + 1
  ),
  in_delimiter,
  ''
);

here are the docs for the string operators for reference: https://dev.mysql.com/doc/refman/8.0/en/string-functions.html

Ulad Kasach
  • 11,558
  • 11
  • 61
  • 87
  • It works, thank you. Made such SF in my DB. But now it seems need to have some checking of parameters to prevent errors in case of wrong parameters like "no delimiters at all" in the `in_array` string. – Troublemaker-DV Mar 06 '21 at 07:06
  • Mostly it works, thank you. But what about situation where the `in_delimiter` itself is the part of "array item"? – Troublemaker-DV Mar 06 '21 at 07:27
0

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

Kshitij Satpute
  • 29
  • 4