MySQL - How to replace random 8 byte number to null

I have following style url in the text body in mysql DB

• Type A: https://www.old_example1.com/12345678/myname
• Type B: https://www.old_example2.com/87654321/yourname

I would like to change the url to below format

• Type A: https://www.new_example1.com/myname
• Type B: http://www.new_example2.com/yourname

For the domain part, it is a simple replacement query, so no big deal. But the 8 byte random number part after the domain is kinda tricky.

I've tried to count text bytes and use substring, but the 'old_example1' and 'old_example2' part is not always consistent bytes. Sometimes it is 1~2 bytes longer or shorter. But the occurence of '/' is consistent, so I have tried to find a way to count '/' and replace numbers values btwn 3rd and 4th '/' to '' (empty value), but can't figure out how to apply such conditions.

You could use REGEXP_REPLACE().

Here's a crude example:

SET @string = 'Some text https://www.old_example1.com/12345678/myname blah blah blah https://www.old_example2.com/87654321/yourname some more blah';

SELECT
    @string AS old_string,
    REGEXP_REPLACE(
        @string,
        'https:\/\/www\.old_example([1|2])\.com(\/[0-9]{8}\/)',
        'https://www.new_example$1.com/'
    ) AS new_string;

Outputs:

Here's a db<>fiddle.