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.
[code language=”sql”]
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]

Code explained
[code language=”sql”]
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
[/code]

Usage:
[code language=”sql”]
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
[/code]