MySQL wildcard replace

Question

Hi i need help with a MySQL query.
Is it possible to do some wildcard replace for a string like the example below?

String:                        example.com/folderone/some/path/to/file.pdf
String:                        example.com/foldertwo/some/path/to/file.pdf
Replace: newsite.com/some/path/to/file.pdf

newsite.com/some/path/to/file.pdf
newsite.com/some/path/to/file.pdf

To remove the folder and change the domain but keep the path. In this case each folder have a different name with a different length.

something like:

update TABLE set COLUMN = replace(COLUMN, 'example.com/%/', 'newsite.com/');

Answer 1

Using SUBSTRING_INDEX:

UPDATE table1 
   SET column1 = REPLACE(
         column1, 
         SUBSTRING_INDEX(column1, '/', 2),
         'newsite.com' 
       )
 WHERE column1 LIKE 'example.com/%/'

This should honour your subfolder structure.