mysql - Find and replace string with placeholders in SQL

Question

Is it possible to search for a string like this:

http://getfile1.posterous.com/getfile/files.posterous.com/temp-2013-03-30/kBpHaviyoumcvCEHnGnrDfwHwmJIrnqrHrxhCnHwfBIHuDsxbemHlxwEwCdi/IMG_6870.jpg

while some parts are placeholders like:

http://getfile%.posterous.com/getfile/files.posterous.com/temp-2013-03-%/%/IMG_6870.jpg

and replace it with:

http://my.domain.com/wordpress/wp-content/uploads/2013/03/IMG_6870.jpg

Thank you for inputs!

Answer 1

MySQL support for regular expressions is very limited, but you can try with a combination of SUBSTRING_INDEX and REPLACE string functions, like this:

UPDATE tablename
SET
  url =
  CONCAT(
    'http://my.domain.com/wordpress/wp-content/uploads/',
    REPLACE(
      REPLACE(
        SUBSTRING_INDEX(
          SUBSTRING_INDEX(url, '/', -3),
          '-',
          3),
        'temp-',
        ''),
      '-',
      '/'),
    '/',
    SUBSTRING_INDEX(url, '/', -1));

But you need to make sure that all strings have the same format. Please see fiddle here.