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:
. 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.
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.