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    |

1 thought on “STRING SPLIT function in MySQL or MariaDB

  1. stilmant

    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.

    Liked by 1 person

    Reply

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.