StackOverflow question #1755408, MySQL

Language: Layout:

-- ------------------------------------------------------------------------------------
    -- USAGE
    -- ------------------------------------------------------------------------------------
    -- SELECT reg_replace(<subject>,
    --                    <pattern>,
    --                    <subpattern>,
    --                    <replacement>,
    --                    <greedy>,
    --                    <minMatchLen>,
    --                    <maxMatchLen>,
    --                    <minSubMatchLen>,
    --                    <maxSubMatchLen>);
    -- where:
    -- <subject> is the string to look in for doing the replacements
    -- <pattern> is the regular expression to match against
    -- <subpattern> is a regular expression to match against within each
    --              portion of text that matches <pattern>
    -- <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
    -- <minSubMatchLen> specifies the minimum match length
    -- <maxSubMatchLen> specifies the maximum match length
    -- (minMatchLen, maxMatchLen, minSubMatchLen and maxSubMatchLen 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, '[A-Z0-9]{3}', '[0-9]', '_', TRUE, 3, 3, 1, 1) FROM tbl;
    DROP FUNCTION IF EXISTS reg_replace;
    CREATE FUNCTION reg_replace(subject VARCHAR(21845), pattern VARCHAR(21845),
      subpattern 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, subpattern, replacement, greedy, minMatchLen, maxMatchLen,
        minSubMatchLen, maxSubMatchLen, result);
      RETURN result;
    END;
    
    DROP PROCEDURE IF EXISTS reg_replace_worker;
    CREATE PROCEDURE reg_replace_worker(subject VARCHAR(21845), pattern VARCHAR(21845),
      subpattern 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 pos
        -- 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 subpattern IS NULL THEN
                SET useRepl = replacement;
              ELSE
                CALL reg_replace_worker(subStr, subpattern, 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 TABLE IF EXISTS tbl;
    CREATE TABLE tbl
    AS
    SELECT '    Lorem ipsum  suspendisse    nisi quis  ante  tristique urna justo cras vitae, ut aliquam viverra ultrices non donec at varius suspendisse, mauris duis hac pretium dapibus quam bibendum augue dapibus.

Vulputate volutpat habitant porta sodales suspendisse himenaeos viverra, tincidunt varius eu primis maecenas orci nulla, sagittis magna quam phasellus consectetur ante non tristique nibh purus blandit turpis congue dolor urna venenatis accumsan.' AS txt;

SELECT txt,
           -- Count the number of gaps between words
           CHAR_LENGTH(txt) -
           CHAR_LENGTH(reg_replace(txt,
                                   '[[:space:]]+', -- Look for a chunk of whitespace
                                   '^.', -- Replace the first character from the chunk
                                   '',   -- Replace with nothing (i.e. remove the character)
                                   TRUE, -- Greedy matching
                                   1,  -- Minimum match length
                                   0,  -- No maximum match length
                                   1,  -- Minimum sub-match length
                                   0   -- No maximum sub-match length
                                   ))
           + 1 -- The word count is 1 more than the number of gaps between words
           - IF (txt REGEXP '^[[:space:]]', 1, 0) -- Exclude whitespace at the start from count
           - IF (txt REGEXP '[[:space:]]$', 1, 0) -- Exclude whitespace at the end from count
           AS `word count`
    FROM tbl;


absolute service time: 4,24 sec