Mysql search & replace with random characters

Question

I have in text some URLS src="https://example.com/public/images/someimage.jpg?itok=WDGFySy" I need remove in every url this garbage token ?itok=WDGFySy, all tokens obviously are random :). I try do it directly in database like this:

UPDATE wp_posts SET post_content = REPLACE(post_content, 'itok=[[:xdigit:]]{8}', '') WHERE post_content LIKE 'itok=[[:xdigit:]]{8}
';

But i cant find any of this tokens like this. LIKE this [a-fA-F0-9]{8} also wont help. Any advice? Thank you for any suggestions.

Possible duplicate of [How to do a regular expression replace in MySQL?](https://stackoverflow.com/questions/986826/how-to-do-a-regular-expression-replace-in-mysql) — Lelio Faieta, Apr 29 '19 at 15:32

fifonik · Answer 1 · 2019-04-30T02:51:22.537

You can only use regex if you have MySQL 8.0.

However, if your links are in separate fields, it is possible to create and use SP to clean them up:

DELIMITER $$
CREATE FUNCTION CLEANUP(
      aString VARCHAR(255)
    , aName VARCHAR(15)
)
RETURNS VARCHAR(255)
BEGIN
    SET @u = SUBSTRING_INDEX(aString, '?', 1);
    SET @q = SUBSTRING_INDEX(aString, '?', -1);

    IF @q = aString THEN
        RETURN aString; -- no '?' char found
    ELSE
        SET @f = LOCATE(CONCAT(aName, '='), @q);
        SET @query = IF(@f > 1, SUBSTR(@q, 1, @f - 1), '');
        IF @f > 0 THEN
            SET @t = LOCATE('&', @q, @f + LENGTH(aName) + 1);
            IF @t > 0 THEN
                SET @query = CONCAT(@query, SUBSTR(@q, @t + 1));
            END IF;
        END IF;

        IF @query = '' THEN
            RETURN @u;
        ELSE
            RETURN CONCAT(@u, '?', TRIM('&' FROM @query));
        END IF;
    END IF;
END
$$
DELIMITER ;

Then:

SELECT
      CLEANUP('https://example.com/public/images/someimage.jpg?itok=WDGFySy&b=2', 'itok')
    , CLEANUP('https://example.com/public/images/someimage.jpg?itok=WDGFySy', 'itok')
    , CLEANUP('https://example.com/public/images/someimage.jpg?a=1&itok=WDGFySy', 'itok')
    , CLEANUP('https://example.com/public/images/someimage.jpg?a=1&itok=WDGFySy&b=2', 'itok')
;

Mysql search & replace with random characters

1 Answers1