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 |