Skip to content

Instantly share code, notes, and snippets.

@bludnic

bludnic/regexp_replace.sql

Created July 23, 2018 14:25
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save bludnic/cc57aa19091a5da2b1dda99341de6700 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save bludnic/cc57aa19091a5da2b1dda99341de6700 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. bludnic created this gist Jul 23, 2018.
    83 changes: 83 additions & 0 deletions regexp_replace.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,83 @@
    /* SELECT test */
    SELECT ID, reg_replace(post_content, '<aside class="mashsb-container(.*)">((.|\n)*)<\/aside>', '', TRUE, 2, 0) as content
    FROM wp_posts
    WHERE post_content REGEXP('<aside class="mashsb-container(.*)">((.|\n)*)<\/aside>')
    LIMIT 1

    /* Update */
    UPDATE wp_posts
    SET post_content = REGEXP_REPLACE('post_content', '<aside class="mashsb-container(.*)">((.|\n)*)<\/aside>', '')
    WHERE ID = 469

    /* Function
    https://stackoverflow.com/questions/986826/how-to-do-a-regular-expression-replace-in-mysql/986870#986870
    */
    DROP FUNCTION IF EXISTS reg_replace;
    DELIMITER //
    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 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
    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//
    DELIMITER ;