STRING SPLIT function in MySQL or MariaDB
To split the string MSSQL has an inbuild function STRING_SPLIT(string, separator). We try to create similar functionality in MySQL or MariaDB with the help of Procedure and a temporary table. MSSQL has a tabular function but MySQL or MariaDB does not. We created a procedure that will put the value into the temporary table.
Create the following procedure: SplitFunction
Create or replace PROCEDURE SplitFunction(IN str varchar(4000), IN delim varchar(10))
begin
DECLARE inipos INT;
DECLARE endpos INT;
DECLARE maxlen INT;
DECLARE fullstr VARCHAR(5000);
DECLARE item VARCHAR(2000);
DROP TEMPORARY TABLE IF EXISTS splittable;
CREATE TEMPORARY table splittable(
value VARCHAR(500) NOT NULL
) ENGINE=Memory;
SET inipos = 1;
SET fullstr = CONCAT(Str, delim);
SET maxlen = LENGTH(fullstr);
REPEAT
SET endpos = LOCATE(delim, fullstr, inipos);
SET item = SUBSTR(fullstr, inipos, endpos - inipos);
IF item <> '' AND item IS NOT NULL THEN
insert into splittable values(item);
END IF;
SET inipos = endpos + 1;
UNTIL inipos >= maxlen END REPEAT;
end
Execute the procedure and use it as follows in procedure or simple:
Session 1:
call splitfunction('A,B,C,D,E',',');
select * from splittable;
value|
-----+
A |
B |
C |
D |
E |
Session 2:
call splitfunction('K,L,M,N,O',',')
select * from splittable;
value|
-----+
K |
L |
M |
N |
O |
You need to use SET maxlen = CHAR_LENGTH(fullstr);
not LENGTH(fullstr); since the other functions used are manipulating the characters and not the bytes.
Else you would have infinite loop if the string contains characters encoded on more than one byte.
LikeLiked by 1 person