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