How to Extract Substrings from a Delimited String in MySQL?

MySQL Substring Extraction using Delimiter

Extracting substrings from a string in MySQL can be achieved using the SUBSTRING_INDEX function. This function lets you specify the delimiter to separate the substrings and the position of the substring you want to extract.

For instance, consider the following table:

item_code	name	colors
102	ball	red,yellow,green
104	balloon	yellow,orange,red

To extract the colors as separate substrings, you can use the following query:

SELECT item_code, name, 
       SUBSTRING_INDEX(SUBSTRING_INDEX(colors, ',', 1), ',', -1) AS color_first,
       SUBSTRING_INDEX(SUBSTRING_INDEX(colors, ',', 2), ',', -1) AS color_second
FROM Product;

The SUBSTRING_INDEX function is applied twice in this query. The first time it's used to find the first substring, and the second time it's used to extract the remaining substrings from the result of the first application.

The resulting output will be as follows:

item_code	name	color_first	color_second
102	ball	red	yellow
104	balloon	yellow	orange

Note that a more verbose version of the query that fetches all the substrings would be:

SELECT item_code, name, 
       SUBSTRING_INDEX(SUBSTRING_INDEX(colors, ',', 1), ',', -1) AS color_first,
       SUBSTRING_INDEX(SUBSTRING_INDEX(colors, ',', 2), ',', -1) AS color_second,
       SUBSTRING_INDEX(SUBSTRING_INDEX(colors, ',', 3), ',', -1) AS color_third
FROM Product;

This longer query retrieves all the substrings from the colors field, using the same pattern as the shorter query.

The above is the detailed content of How to Extract Substrings from a Delimited String in MySQL?. For more information, please follow other related articles on the PHP Chinese website!