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:
- Federico Cargnelutti - MySQL Split String Function
- Stackoverflow - MYSQL - Array data type, split string
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.