StackOverflow question #5361457, MySQL

Language: Layout:

-- ------------------------------------------------------------------------------------
    -- USAGE
    -- ------------------------------------------------------------------------------------
    -- SELECT reg_replace(<subject>,
    --                    <pattern>,
    --                    <replacement>,
    --                    <greedy>,
    --                    <minMatchLen>,
    --                    <maxMatchLen>);
    -- where:
    -- <subject> is the string to look in for doing the replacements
    -- <pattern> is the regular expression to match against
    -- <replacement> is the replacement string
    -- <greedy> is TRUE for greedy matching or FALSE for non-greedy matching
    -- <minMatchLen> specifies the minimum match length
    -- <maxMatchLen> specifies the maximum match length
    -- (minMatchLen and maxMatchLen are used to improve efficiency but are
    --  optional and can be set to 0 or NULL if not known/required)
    -- Example:
    -- SELECT reg_replace(txt, '^[Tt][^ ]* ', 'a', TRUE, 2, 0) FROM tbl;
    DROP FUNCTION IF EXISTS reg_replace;
    CREATE FUNCTION reg_replace(subject VARCHAR(21845), pattern VARCHAR(21845),
      replacement VARCHAR(21845), greedy BOOLEAN, minMatchLen INT, maxMatchLen INT)
    RETURNS VARCHAR(21845) DETERMINISTIC BEGIN 
      DECLARE result, subStr, usePattern VARCHAR(21845); 
      DECLARE startPos, prevStartPos, startInc, len, lenInc INT;
      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
              SET result = IF(startInc = 1,
                              CONCAT(result, replacement), CONCAT(replacement, 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;
      RETURN result;
    END;
    
    DROP TABLE IF EXISTS tbl;
    CREATE TABLE tbl
    AS
    SELECT 'No digits.' AS txt UNION ALL
    SELECT 'One (1) digit in the middle.' AS txt UNION ALL
    SELECT '1 digit at the start.' AS txt UNION ALL
    SELECT 'A digit at the end: 1' AS txt UNION ALL
    SELECT 'Two digits not together: 1 and 2.' AS txt UNION ALL
    SELECT 'Two digits together: 12.' AS txt UNION ALL
    SELECT 'Three digits together: 123.' AS txt UNION ALL
    SELECT 'Two pairs of digits together: 12 and 34.' UNION ALL
    SELECT 'Two groups of three digits together: 123 and 456.' AS txt;
    
    SELECT txt,
           reg_replace(txt,
                       '[^0-9]+',
                       ',',
                       TRUE,
                       1, -- Min match length
                       0 -- No max match length
                       ) AS `extracted`,
           reg_replace(reg_replace(
                                   reg_replace(
                                               reg_replace(txt,
                                                           '[^0-9]+',
                                                           ',',
                                                           TRUE,
                                                           1, -- Min match length
                                                           0 -- No max match length
                                                           ),
                                               '([0-9]{3,}|,[0-9],)',
                                               '',
                                               TRUE,
                                               1, -- Min match length
                                               0 -- No max match length
                                               ),
                                   '^[0-9],',
                                   '',
                                   TRUE,
                                   1, -- Min match length
                                   0 -- No max match length
                                   ),
                       ',[0-9]$',
                       '',
                       TRUE,
                       1, -- Min match length
                       0 -- No max match length
                       ) AS `only pairs`,
           reg_replace(reg_replace(reg_replace(reg_replace(reg_replace(reg_replace(reg_replace(txt,
                                                                                               '[^0-9]+',
                                                                                               ',',
                                                                                               TRUE,
                                                                                               1, -- Min match length
                                                                                               0 -- No max match length
                                                                                               ),
                                                                                   '([0-9]{3,}|,[0-9],)',
                                                                                   '',
                                                                                   TRUE,
                                                                                   1, -- Min match length
                                                                                   0 -- No max match length
                                                                                   ),
                                                                       '^[0-9],',
                                                                       '',
                                                                       TRUE,
                                                                       1, -- Min match length
                                                                       0 -- No max match length
                                                                       ),
                                                           ',[0-9]$',
                                                           '',
                                                           TRUE,
                                                           1, -- Min match length
                                                           0 -- No max match length
                                                           ),
                                               ',{2,}',
                                               ',',
                                               TRUE,
                                               1, -- Min match length
                                               0 -- No max match length
                                               ),
                                   '^,',
                                   '',
                                   TRUE,
                                   1, -- Min match length
                                   0 -- No max match length
                                   ),
                       ',$',
                       '',
                       TRUE,
                       1, -- Min match length
                       0 -- No max match length
                       ) AS `csv`
    FROM tbl;


absolute service time: 0,37 sec