1

A client of mine has used Velvet Blues Update URLs plugin (https://en-gb.wordpress.org/plugins/velvet-blues-update-urls/) on their site and have broken pretty much all of their links...

So now the links look like: www.site.compost-name/ instead of www.site.com/post-name/.

This is a live site, so I need to be very careful how I fix it so I don't make matters worse.

So I know I need some kind of Regex to check for the site url followed by either letters or numbers without the /.

I can view a lot f the broken links by running a query like: SELECT * FROM wp_postmeta WHERE meta_value REGEXP 'site.com[0-9 a-z A-Z]'.

And typically when updating something in the DB I would run a query like: UPDATE wp_postmeta SET meta_value = replace(meta_value,'http://www.oldurl','http://www.newurl');.

But from what I have found from my research is that MySQL doesn't support a kind of preg_replace functionality (How to do a regular expression replace in MySQL?).

I guess one approach might be to add a filter to wp_content and fix it before it is loaded on the page, but this only masks the problem and doesn't necessarily fix it.

I'm a little stumped on this one because there are thousands of broken links so there is a lot of scope for a search and replace query to go awry and potentially make things worse, plus my regex knowledge is limited.

MP_Webby
  • 916
  • 1
  • 11
  • 35

2 Answers2

1

You can use NOT LIKE operand for this. For example,

UPDATE wp_postmeta 
SET meta_value=REPLACE(meta_value,'site.com','site.com/') 
WHERE meta_value LIKE '%site.com%' 
AND 
meta_value NOT LIKE '%site.com/%'

Of course, just in case, you need to run SELECT query first and review the results. Because it is not ideal approach and may cause some rare issues.

Elvin Haci
  • 3,503
  • 1
  • 17
  • 22
0

sed is your best friend for things like this.

sed 's/oldurl/newurl/g' -r /project/root will update every plain text instance in your entire project.

Luke Mlsna
  • 468
  • 4
  • 16