MySQL Split String Function Fix (split_str)
@shakedko
IF AN EXPERT SAYS IT CAN'T BE DONE GET ANOTHER EXPERT.
- DAVID BEN-GURION

MySQL Split String Function Fix (split_str)

While working on one my projects, I was needed to use split_str function in MySQL. I Googled for it and found two answers (which are exactly the same - one is leading to the other): 

Federico Cargnelutti - MySQL Split String Function

Stackoverflow - MYSQL - Array data type, split string

Everything worked great while using different delimiters, such as: "," , "||", "@@@", "###" or any other delimiter. I was using delimiters which their length > 1, exmaple:

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

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

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

So how do we fix it?  Seems that we need to change split_str function, all we have to do is to use CHAR_LENGTH() and not LENGTH(). There is a difference between those functions which you can read at MySQL.com website 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".

 Enough talking. This is the function after my little fix: 

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 thank to Federico Cargnelutti that helped us by writing the above function.

Shak.

Work In Progress 🚧
Discipline