Get Value from Delimited Array in MySQL

Problem Getting Values From Delimited Array in MySQL

MySQL does not have an array type, and if you need to implement arrays, a delimted string is a simple way. If you are using strings to pass a delimited array in MySQL, you have run into the problem of getting a value out at a index. The equivalent in a programming language such as Java would be:

array[1]

. MySQL has something close with SUBSTRING_INDEX. It can get a substring up to the nth location of a string. Example:

SELECT SUBSTRING_INDEX('my|delimited|string', '|', 2);
my|delimited

As you can see this will get the subtring up to the second occurrence of ‘|’. If you wanted to get the second value in this delimited array, you could do the following:

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('my|delimited|string', '|', 2), '|', -1);
delimited

This is a lot of code to do a simple operation and can quickly clutter up your queries.

Solution

I buried this code into the following function:

CREATE FUNCTION getValueFromArray(
_array TEXT,
_del VARCHAR(255),
_value INT UNSIGNED
)
RETURNS VARCHAR(255)
NO SQL
DETERMINISTIC
SQL SECURITY DEFINER
BEGIN

IF _array IS NULL || _array = '' || _del IS NULL || _del = '' || _value IS NULL || _value > stringOccurrences(_array, _del) THEN
    RETURN '';
ELSE
    RETURN SUBSTRING_INDEX(SUBSTRING_INDEX(_array, _del, _value + 1), _del, -1);
END IF;

END~

This function will handle null and empty strings (empty arrays). To complete the example above, the following query can be made:

SELECT getValueFromArray('my|delimited|string', '|', 1);
delimited

Hopefully this will help clear up queries and have better logical flow.

{o}
Otreva is a custom software product development company focusing on user experience, responsive web development, & mobile application development.

Interested in seeing what it costs to build an app?

Start Quote See Stats
224 Wyoming Ave. #100
Scranton, PA 18503

You've found the secret footer!

Tweets about @Otreva

Or check us out on:

Shopify Experts
Awwwards