STRING SPLIT function in MySQL or MariaDB

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    |

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.