MySQL Split String Function Fix (split_str)

Splitting MySQL strings with split_str and multibyte delimiters.

I had to use MySQL split_str in one of my project. I Googled and found two answers:

I used it and everything worked great while using different delimiters, such as: "," , "||", "@@@", "###" etc. However I had to use different delimiters i.e strlen(delimiter) > 1. For example:

select split_str("ABC,,BA,,abc",",,",3);
//result: “abc”

Seems good, ha? But then I"v noticed that there is a problem. When a full string contains two bytes characters (e.g: ¼), everything is breaking a part. For exmaple:

select split_str("ABC¼,,BA,,abc",",,",3);
//result: “,abc” (delimiter was still there)

How do we fix it? I ha to change split_str function. Instead of using LENGTH() I had to use CHAR_LENGTH.

You can read about the difference between those functions at
MySQL.com or just read the following quote:

"CHAR_LENGTH(str") Returns the length of the string "str", measured in characters. A multi-byte character counts as a single character. This means that for a string containing five two-byte characters,"LENGTH()" returns "10", whereas"CHAR_LENGTH()" returns "5".

Adding everything togehter:

CREATE FUNCTION SPLIT_STR(
  x VARCHAR(255),
  delim VARCHAR(12),
  pos INT
)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
       CHAR_LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
       delim, "");

Then using the same example I"v used above:

select split_str("ABC¼,,BA,,abc",",,",3);
//result: “abc"

I want to say thanks to Federico Cargnelutti that helped us by writing the above function.