Find and replace phpMyAdmin-CodePudding

I have around 20k URLs using this pattern bellow and I am trying to replace and remove this fffd8ca225794d4c3c9f33a2ec321828of these URLs using phpMyAdmin

This is just an example, all these strings are random

What it is https://www.xxxx.com/file/0/fffd8ca225794d4c3c9f33a2ec321828/740000/740640/screenshots/1.jpg

What I need https://www.xxxx.com/contents/sources/740000/740640/screenshots/1.jpg

Could someone help me?

CodePudding user response：

You can use simple UPDATE query with REPLACE function:

    UPDATE <your table name> 
    SET <your field name> = REPLACE(
        <your field name>, 
        'fffd8ca225794d4c3c9f33a2ec321828/', -- find this substring
        '' -- replace by empty string
    );

Test MySQL REPLACE online

Since MySQL 8.0 you can use REGEXP_REPLACE function

UPDATE urls SET url = REGEXP_REPLACE(
  url, 
  'file/0/[a-z0-9] /', 'contents/sources/
');

MySQL REGEXP_REPLACE test