StackOverflow question #56166445, 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 table_name;
    CREATE TABLE table_name (id INT, col_a VARCHAR(255), col_b VARCHAR(255), col_c VARCHAR(255));
    INSERT INTO table_name (id, col_a, col_b, col_c) VALUES (1, '  apple  \t   orange plum', 'red \t green blue', 'bill dave sue    ');
    INSERT INTO table_name (id, col_a, col_b, col_c) VALUES (2, '  orange   plum  banana', 'yellow    red', 'frank     james');
    INSERT INTO table_name (id, col_a, col_b, col_c) VALUES (3, 'kiwi  \t fruit apple', 'green pink', 'bill sarah-jane \t   frank');

SELECT DISTINCT word
    FROM 
    (SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(words, '¬', digits.idx + 1), '¬', -1) word
     FROM
     (SELECT reg_replace(LOWER(TRIM(CONCAT(col_a, ' ', col_b, ' ', col_c))),
                         '[[:space:]]+',
                         '¬',
                         TRUE,
                         1,
                         0) AS words
      FROM table_name) delimited
     INNER JOIN
     (SELECT @row := @row + 1 as idx FROM 
      (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
                UNION ALL SELECT 6 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8
                UNION ALL SELECT 9) t1,
      (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
                UNION ALL SELECT 6 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8
                UNION ALL SELECT 9) t2, 
      (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
                UNION ALL SELECT 6 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8
                UNION ALL SELECT 9) t3, 
      (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
                UNION ALL SELECT 6 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8
                UNION ALL SELECT 9) t4, 
      (SELECT @row := -1) t5) digits
     ON LENGTH(REPLACE(words, '¬' , '')) <= LENGTH(words) - digits.idx) subq
    WHERE CHAR_LENGTH(word) >= 5
    ORDER BY word


absolute service time: 0,5 sec