Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL substring extraction using delimiter

Tags:

split

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.

like image 470
Bibin Jose Avatar asked Jan 25 '16 12:01

Bibin Jose


People also ask

How do I separate text in MySQL?

To split a string in MySQL, you need to make use of the SUBSTRING_INDEX function that is provided by MySQL. The SUBSTRING_INDEX() function allows you to extract a part of a complete string. The syntax of the function is as follows: SUBSTRING_INDEX(expression, delimiter, count);

What is SUBSTRING_INDEX in MySQL?

The SUBSTRING_INDEX() function returns a substring of a string before a specified number of delimiter occurs.

Is there a split function in MySQL?

MySQL Split concept comes into the picture if you are intended to split the string. In MySQL, we use SUBSTRING_INDEX() to split the string. It usually consists of three arguments i.e., string, delimiter, and position. The string value will be split based on the position.

How do I find the index of a character in a string in MySQL?

The POSITION() function returns the position of the first occurrence of a substring in a string. If the substring is not found within the original string, this function returns 0. This function performs a case-insensitive search. Note: The LOCATE() function is equal to the POSITION() function.


1 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; 
like image 113
Sameer Mirji Avatar answered Oct 13 '22 00:10

Sameer Mirji