victoraslt_regexreplace, 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 'The quick brown fox jumps over the lazy dog' AS txt;

SELECT txt,
           reg_replace(txt,
                       'q.*k b.*n',
                       'slow red',
                       TRUE,
                       5,
                       0
                       ) AS test1,
           reg_replace(txt,
                       '[Tt][^ ]* ',
                       'a ',
                       TRUE,
                       2,
                       0
                       ) AS test2,
           reg_replace(txt,
                       '^[Tt][^ ]* ',
                       'a ',
                       TRUE,
                       2,
                       0
                       ) AS test3,
           reg_replace(txt,
                       'd.*g$',
                       'cat',
                       TRUE,
                       2,
                       0
                       ) AS test4,
           reg_replace(txt,
                       '[[:space:]]+', -- See http://stackoverflow.com/questions/15558172#15558268
                       '_',
                       TRUE,
                       1,
                       0
                       ) AS test5,
           reg_replace(txt,
                       'l.+',
                       'log',
                       TRUE,
                       2,
                       0
                       ) AS test6,
           reg_replace(txt,
                       '^Th.*og$',
                       'No foxes or dogs here!',
                       TRUE,
                       4,
                       0
                       ) AS test7
    FROM tbl;


absolute service time: 0,47 sec