MySQL creating a concat_WS delimiter function, one part not working I have a function that looks like this:

CREATE FUNCTION
CONCAT_UNTIL( x LONGTEXT, delim CHAR(1), repeatEvery SMALLINT) RETURNS LONGTEXT
LANGUAGE SQL
NOT DETERMINISTIC
READS SQL DATA
BEGIN
    DECLARE len INT UNSIGNED;
    DECLARE erg LONGTEXT;
    SET len = CHARACTER_LENGTH(x);
    REPEAT
        SET erg = CONCAT_WS(delim, SUBSTRING(x, len, repeatEvery), erg);
        SET len = len - repeatEvery;
   UNTIL len &lt;= 0 END REPEAT;
   return erg;
END;


I use the function like this:

WHEN CHARACTER_LENGTH(mac) = 12
    THEN
        CONCAT(CONCAT_UNTIL(UPPER(mac), ':', 2), ',')
END AS fullMac


So the field "mac" looks like this:

555FFFEE1001
555FFFEE1004
555FFFEE1005
555FFFEE1006
555FFFEE1007
555FFFEE1008
555FFFEE1009
555FFFEE1019


So I expect to see this:

55:FF:FE:E1:00:01,
55:FF:FE:E1:00:04,
55:FF:FE:E1:00:05,
55:FF:FE:E1:00:06,
55:FF:FE:E1:00:07,
55:FF:FE:E1:00:08,
55:FF:FE:E1:00:09,
55:FF:FE:E1:01:19,


But instead when I run the function I get this:

55:FF:FE:E1:00:1,
55:FF:FE:E1:00:4,
55:FF:FE:E1:00:5,
55:FF:FE:E1:00:6,
55:FF:FE:E1:00:7,
55:FF:FE:E1:00:8,
55:FF:FE:E1:00:9,
55:FF:FE:E1:01:9,


I've been messing around with all sorts of different fields, but haven't had success.  What is my logical error and can someone help me with the correct function call?
http://sqlfiddle.com/#!9/ba747/1

You have off-by-one error (in the function and in the expected output too)

555FFFEE1001 should expand to
55:5F:FF:EE:10:01 and not
55:FF:FE:E1:00:01


So all your computations were offset by one character - in the SUBSTRING inside the loop of your function you try to get "last" two characters of a mac, but there you put len as poswhich means you want to start with 12th character - but thats the last one so you cannot get two of them - this means that you loose the first character in the end.

You have to dynamicaly accout for how many characters you want to cut from the end when giving the pos:

CREATE FUNCTION
CONCAT_UNTIL( x LONGTEXT, delim CHAR(1), repeatEvery SMALLINT) RETURNS LONGTEXT
LANGUAGE SQL
NOT DETERMINISTIC
READS SQL DATA
BEGIN
    DECLARE len int unsigned;
    DECLARE erg LONGTEXT;
    SET len = CHARACTER_LENGTH(x);
    REPEAT
        SET erg = CONCAT_WS(delim, SUBSTRING(x, len - repeatEvery + 1, repeatEvery), erg);
        SET len = len - repeatEvery;
   UNTIL len &lt;= 0 END REPEAT;
   return erg;
END//