custom mysql string delimited function

Objective: To create a mysql function to extract the specified position of the string element from a string delimited string in mysql select field. For example, provided a string “a,bb,ccc,dddd,eeeee”, a comma as the string delimiter and a integer represents one of the sub string, the function will be able to return the specified sub string.

The string delimited mysql function.

DELIMITER $$
CREATE FUNCTION DELIMITED_INDEX(str VARCHAR(255), delim VARCHAR(10), pos INT)
	RETURNS VARCHAR(255)
	DETERMINISTIC
BEGIN
	DECLARE sub_string VARCHAR(255);
	DECLARE pre_string_len INT;
	DECLARE delimited_str_element VARCHAR(255);
	
	SET sub_string = SUBSTRING_INDEX(str, delim, pos);
	SET pre_string_len = CHAR_LENGTH(SUBSTRING_INDEX(str, delim, pos-1));
	SET delimited_str_element = SUBSTR(sub_string, pre_string_len+1);
	SET delimited_str_element = REPLACE(delimited_str_element, delim, '');

	RETURN delimited_str_element;
END
$$ 
DELIMITER ; 

Code explained

DELIMITER $$ -- temporary set the default mysql query delimiter to $$
CREATE FUNCTION DELIMITED_INDEX(str VARCHAR(255), delim VARCHAR(10), pos INT) -- Declare the function with three parameters
	RETURNS VARCHAR(255) -- Specify the return type will be varchar
	DETERMINISTIC -- Flag the function as deterministic function
BEGIN
        -- declare function local variables
	DECLARE sub_string VARCHAR(255); 
	DECLARE pre_string_len INT;
	DECLARE delimited_str_element VARCHAR(255);

        -- process the string to get the result	
	SET sub_string = SUBSTRING_INDEX(str, delim, pos);
	SET pre_string_len = CHAR_LENGTH(SUBSTRING_INDEX(str, delim, pos-1));
	SET delimited_str_element = SUBSTR(sub_string, pre_string_len+1);
	SET delimited_str_element = REPLACE(delimited_str_element, delim, '');

	RETURN delimited_str_element; -- return the result
END
$$ -- end of the mysql query
DELIMITER ; -- set the mysql query delimiter back to semicolon

SELECT SUBSTRING_INDEX("a,bb,ccc,dddd,eeee", ",", 1) AS str; -- a
SELECT SUBSTRING_INDEX("a,bb,ccc,dddd,eeee", ",", 2) AS str; -- a,bb
SELECT SUBSTRING_INDEX("a,bb,ccc,dddd,eeee", ",", 3) AS str; -- a,bb,cc
SELECT CHAR_LENGTH("a,bb") AS str_length; -- 4
SELECT SUBSTR("12345678", 1) AS str; -- 12345678
SELECT SUBSTR("12345678", 2) AS str; -- 2345678
SELECT SUBSTR("12345678", 3) AS str; -- 345678
SELECT SUBSTR("12345678", 4) AS str; -- 45678
SELECT REPLACE("a,bb,ccc", ",", '') AS str; -- abbccc
SELECT REPLACE("a|bb|ccc", "|", '') AS str; -- abbccc
SELECT REPLACE("a\nbb\nccc", "\n", '') AS str; -- abbccc

Usage:

SELECT DELIMITED_INDEX("a,bb,ccc,dddd,eeee", ",", 0) AS extracted_str; -- empty string
SELECT DELIMITED_INDEX("a,bb,ccc,dddd,eeee", ",", 1) AS extracted_str; -- a
SELECT DELIMITED_INDEX("a,bb,ccc,dddd,eeee", ",", 2) AS extracted_str; -- bb
SELECT DELIMITED_INDEX("a,bb,ccc,dddd,eeee", ",", 3) AS extracted_str; -- ccc
SELECT DELIMITED_INDEX("a,bb,ccc,dddd,eeee", ",", 4) AS extracted_str; -- dddd
SELECT DELIMITED_INDEX("a,bb,ccc,dddd,eeee", ",", 5) AS extracted_str; -- eeeee
SELECT DELIMITED_INDEX("a,bb,ccc,dddd,eeee", ",", 6) AS extracted_str; -- empty string