0

Initially the website is built with wordpress. The domain got changed from https://olddomain.com to https://newdomain.com and I have to migrate to DB as well.

In the Post content there is image url like

  • https://olddomain.com/wp-content/upload/2023/01/imagename.png
  • https://olddomain.com/wp-content/upload/xxxx/xx/imagename.png etc.

what i want to achieve is replace https://olddomain.com/wp-content/upload/2023/01/imagename.png with https://newdomain.com/public/media/images/imagename.png

I tried by using REPLACE via sql query as following:

Update  posts
Set post_description = replace(post_description, 'https://olddomain.com/wp-content/upload/', 'https://newdomain.com/public/media/images/')

The https://olddomain.com/wp-content/upload/xxxx/xx/imagename.png has become https://newdomain.com/public/media/images/xxx/xx/imagename.png

Now the challenge is replacing /xxxx/xx with ''. Is it possible with SQL replace or what could be alternate solution.

Thanks!

Ram Chander
  • 1,088
  • 2
  • 18
  • 36
  • 1
    https://stackoverflow.com/questions/986826/how-to-do-a-regular-expression-replace-in-mysql should be able to give you some inspiration. – CBroe Apr 05 '23 at 08:14

2 Answers2

1

Try this mysql query:

UPDATE posts
SET post_description = REPLACE(
    REPLACE(
        post_description, 
        'https://olddomain.com/wp-content/upload/', 
        'https://newdomain.com/public/media/images/'
    ), 
    SUBSTRING(post_description, LOCATE('https://olddomain.com/wp-content/upload/', post_description) + LENGTH('https://olddomain.com/wp-content/upload/'), 8),
    ''
)
tripleee
  • 175,061
  • 34
  • 275
  • 318
Rajendra Singh
  • 26
  • 1
0

Here's an example query that should work:

UPDATE posts
SET post_description = REPLACE(post_description,
'https://olddomain.com/wp-content/uploads/',
'https://newdomain.com/public/media/images/')
, post_description = CONCAT(SUBSTRING_INDEX(post_description, '/', -3),
'/',
SUBSTRING_INDEX(post_description, '/', -1))
WHERE post_description LIKE '%https://olddomain.com/wp-content/uploads/%';
Ashok kumawat
  • 492
  • 3
  • 15
  • Thank you for the response, but it doesn't working, I have to run just replace image url, rest of the content will remain same. – Ram Chander Apr 05 '23 at 09:28