MySQL complex REGEXP find and replace

Question

I want to run a regexp find and replace on a wordpress database, specifically the in wp_posts table, in the post_content column. I have several affiliate URLs scattered in the text of the post_content column, and I need to replace them with new affiliate URLs. The problem is that the current URLs have multiple params that may vary depending on the URL, and they are not always in the same order. The only param that is consistent across all URLs id the store_id param.

Example of a current URL: https://www.affialite.com/redirect.php?tt=el&store_id=this_is_the_store_id&param1=this_is_param_1&url=https://redirect_the_visitor_to_this_url.com&c_id=id_of_the_campaign

Example of how it should look after the find/replace operation: https://newaffiliate.net/c/1234/5678/9012?campaign=id_of_the_campaign&u=https://redirect_the_visitor_to_this_url.com

The query must find the affiliate links, replace the first part of the URL with the new affiliate url, capture the value of param url and match it to the new u param, and capture the value of the c_id param and match it to the new campaign param.

I have close do zero experience with mysql, other than the regular CRUD operations, so I was using ChatGPT to help me build a query for this. The closest I got is the one below, but the resulting URL has the params in all the wrong places.

UPDATE wp_posts
SET post_content =
    REGEXP_REPLACE(
        post_content,
        'https:\/\/www\.affiliate\.com\/redirect\.php\?(?:.*?&|.*?&)(store_id=this_is_the_store_id)(?:&.*?|&)(.*?)',
        CONCAT(
            'https://newaffiliate.net/c/1234/5678/9012?',
            'campaign=',
            REGEXP_SUBSTR(
                REGEXP_SUBSTR(CONCAT('&', '\\2', '&'), '&c_id=([^&]+)'),
                '[^=]+$'
            ),
            '&u=',
            REGEXP_SUBSTR(
                REGEXP_SUBSTR(CONCAT('&', '\\2', '&'), '&url=([^&]+)'),
                '[^=]+$'
            )
        )
    )
WHERE post_content REGEXP 'https:\/\/www\.affiliate\.com\/click\.php\?(?:.*?&|.*?&)(store_id=this_is_the_store_id)(?:&.*?|&)(.*?)';

Here's the example of a wrong resulting URL: https://newaffiliate.com?campaing=&u=param1=this_is_param_1&url=https://redirect_the_visitor_to_this_url.com&c_id=id_of_the_campaign

Any insight on what's wrong with the query? Any help is very much appreciated.

Thank you all.

Answer 1

@BillKarwin has explained the issues with, and how to simplify, your current query very well. However, if you are running MySQL >= 8.0.4, you can modify his suggested query using capturing groups inside lookaheads, so that you can deal with the parameters in any order. For example:

CREATE TABLE wp_posts (post_content text);
INSERT INTO wp_posts VALUES
  ('https://www.affiliate.com/redirect.php?tt=el&store_id=this_is_the_store_id&param1=this_is_param_1&url=https://redirect_the_visitor_to_this_url.com&c_id=id_of_the_campaign'),
  ('https://www.affiliate.com/redirect.php?tt=el&store_id=this_is_the_store_id&c_id=id_of_the_campaign&param1=this_is_param_1&url=https://redirect_the_visitor_to_this_url.com')
  ('https://www.affiliate.com/redirect.php?tt=el&store_id=this_is_another_store_id&c_id=id_of_the_campaign&param1=this_is_param_1&url=https://redirect_the_visitor_to_this_url.com')
;

UPDATE wp_posts
SET post_content = REGEXP_REPLACE(
  post_content,
  'https://www\\.affiliate\\.com/redirect\\.php\\?(?=.*\\bstore_id=this_is_the_store_id\\b)(?=.*\\bc_id=([^&]*))(?=.*\\burl=([^&]*)).*',
  'https://newaffiliate.net/c/1234/5678/9012?campaign=$1&u=$2'
)

SELECT *
FROM wp_posts

Output:

post_content
https://newaffiliate.net/c/1234/5678/9012?campaign=id_of_the_campaign&u=https://redirect_the_visitor_to_this_url.com
https://newaffiliate.net/c/1234/5678/9012?campaign=id_of_the_campaign&u=https://redirect_the_visitor_to_this_url.com
https://www.affiliate.com/redirect.php?tt=el&store_id=this_is_another_store_id&c_id=id_of_the_campaign&param1=this_is_param_1&url=https://redirect_the_visitor_to_this_url.com

Demo on dbfiddle.uk

Notes:

• you don't need a WHERE clause on the UPDATE; if the regex does not match then no replacement will occur.
• you don't need to escape /; but you should escape . as that can match any character
• it's safer to use a \b in front of c_id and url so they don't match (for example) ac_id or curl
• to escape a value you need \\ in your string due to the first \ being absorbed by string parsing.