StackOverflow question #45555747, MySQL

Language: Layout:

DROP TABLE IF EXISTS tbl;

CREATE TABLE tbl (id INT, options TEXT);
    INSERT INTO tbl (id, options) VALUES (1, 'A|10|B|20|C|30'), (2, 'A|Positive|B|Negative');

DROP PROCEDURE IF EXISTS reg_replace_worker;
    CREATE PROCEDURE reg_replace_worker(subject VARCHAR(21845), pattern VARCHAR(21845),
      pattern2 VARCHAR(21845), replacement VARCHAR(21845), greedy BOOLEAN, minMatchLen INT,
      maxMatchLen INT, minSubMatchLen INT, maxSubMatchLen INT, OUT result VARCHAR(21845))
    BEGIN
      DECLARE subStr, usePattern, useRepl VARCHAR(21845);
      DECLARE startPos, prevStartPos, startInc, len, lenInc INT;
      SET @@SESSION.max_sp_recursion_depth = 2;
      IF subject REGEXP pattern THEN
        SET result = '';
        -- Sanitize input parameter values
        SET minMatchLen = IF(minMatchLen < 1, 1, minMatchLen);
        SET maxMatchLen = IF(maxMatchLen < 1 OR maxMatchLen > CHAR_LENGTH(subject),
                             CHAR_LENGTH(subject), maxMatchLen);
        -- Set the pattern to use to match an entire string rather than part of a string
        SET usePattern = IF (LEFT(pattern, 1) = '^', pattern, CONCAT('^', pattern));
        SET usePattern = IF (RIGHT(pattern, 1) = '$', usePattern, CONCAT(usePattern, '$'));
        -- Set start position to 1 if pattern starts with ^ or doesn't end with $.
        IF LEFT(pattern, 1) = '^' OR RIGHT(pattern, 1) <> '$' THEN
          SET startPos = 1, startInc = 1;
        -- Otherwise (i.e. pattern ends with $ but doesn't start with ^): Set start position
        -- to the min or max match length from the end (depending on "greedy" flag).
        ELSEIF greedy THEN
          SET startPos = CHAR_LENGTH(subject) - maxMatchLen + 1, startInc = 1;
        ELSE
          SET startPos = CHAR_LENGTH(subject) - minMatchLen + 1, startInc = -1;
        END IF;
        WHILE startPos >= 1 AND startPos <= CHAR_LENGTH(subject)
          AND startPos + minMatchLen - 1 <= CHAR_LENGTH(subject)
          AND !(LEFT(pattern, 1) = '^' AND startPos <> 1)
          AND !(RIGHT(pattern, 1) = '$'
                AND startPos + maxMatchLen - 1 < CHAR_LENGTH(subject)) DO
          -- Set start length to maximum if matching greedily or pattern ends with $.
          -- Otherwise set starting length to the minimum match length.
          IF greedy OR RIGHT(pattern, 1) = '$' THEN
            SET len = LEAST(CHAR_LENGTH(subject) - startPos + 1, maxMatchLen), lenInc = -1;
          ELSE
            SET len = minMatchLen, lenInc = 1;
          END IF;
          SET prevStartPos = startPos;
          lenLoop: WHILE len >= 1 AND len <= maxMatchLen
                     AND startPos + len - 1 <= CHAR_LENGTH(subject)
                     AND !(RIGHT(pattern, 1) = '$' 
                           AND startPos + len - 1 <> CHAR_LENGTH(subject)) DO
            SET subStr = SUBSTRING(subject, startPos, len);
            IF subStr REGEXP usePattern THEN
              IF pattern2 IS NULL THEN
                SET useRepl = replacement;
              ELSE
                CALL reg_replace_worker(subStr, pattern2, NULL, replacement, greedy, 
                              minSubMatchLen, maxSubMatchLen, NULL, NULL, useRepl);
              END IF;
              SET result = IF(startInc = 1,
                              CONCAT(result, useRepl), CONCAT(useRepl, result));
              SET startPos = startPos + startInc * len;
              LEAVE lenLoop;
            END IF;
            SET len = len + lenInc;
          END WHILE;
          IF (startPos = prevStartPos) THEN
            SET result = IF(startInc = 1, CONCAT(result, SUBSTRING(subject, startPos, 1)),
                            CONCAT(SUBSTRING(subject, startPos, 1), result));
            SET startPos = startPos + startInc;
          END IF;
        END WHILE;
        IF startInc = 1 AND startPos <= CHAR_LENGTH(subject) THEN
          SET result = CONCAT(result, RIGHT(subject, CHAR_LENGTH(subject) + 1 - startPos));
        ELSEIF startInc = -1 AND startPos >= 1 THEN
          SET result = CONCAT(LEFT(subject, startPos), result);
        END IF;
      ELSE
        SET result = subject;
      END IF;
    END;

DROP FUNCTION IF EXISTS reg_replace;
    CREATE FUNCTION reg_replace(subject VARCHAR(21845), pattern VARCHAR(21845),
      pattern2 VARCHAR(21845), replacement VARCHAR(21845), greedy BOOLEAN, minMatchLen INT,
      maxMatchLen INT, minSubMatchLen INT, maxSubMatchLen INT)
    RETURNS VARCHAR(21845) DETERMINISTIC BEGIN
      DECLARE result VARCHAR(21845);
      CALL reg_replace_worker(subject, pattern, pattern2, replacement, greedy, minMatchLen,
                              maxMatchLen, minSubMatchLen, maxSubMatchLen, result);
      RETURN result;
    END;
    
    SELECT id,
           options AS `before`,
           reg_replace(options,
                       '\\|.*\\|', -- 2 pipe symbols with any text in between
                       '\\|$',     -- Replace the second pipe symbol
                       ',',        -- Replace with a comma
                       FALSE,      -- Non-greedy matching
                       2,          -- Min match length = 2 (2 pipe symbols)
                       0,          -- No max match length
                       0,          -- Min sub-match length = 1 (1 pipe symbol)
                       0           -- Max sub-match length = 1 (1 pipe symbol)
                       ) AS `after`
    FROM tbl;


absolute service time: 0,35 sec