0

I have changed our permalink structure for a better SEO Score. Our old was day and name like https://domain.tld/%year%/%month%/%day%/%postname%. On our site are around 2600 posts with links to other posts from us. In all these posts could be links to ohter pages/posts.

How do I bulk replace this old permalinks to the new format, from https://domain.tld/%year%/%month%/%day%/%postname% to https://domain.tld/%postname%.

Improve this question
5
  • I don't know that this can be done via an SQL query since it would need to figure out the month/day/date. The best I can see is that for each post you do a search replace of the old URL to the new URL, but that would mean 2600 search replaces. Nobody said you had to create those 2600 search replaces by hand though, but you will want to do it via WP CLI or a search replace tool since the string length is different, any serialised PHP values with those URLs would be corrupted by a raw SQL search replace
    – Tom J Nowell ♦
    Apr 12 at 9:52
  • i ask me why, when you change the links on /wp-admin/options-permalink.php to your fav post wp should relink your posts
    – StefanBD
    Apr 12 at 16:20
  • I've tried, but sadly there is no relink. If a post contains the old URL it ends in a 404 Page not found error.
    – Stefan
    Apr 12 at 16:29
  • did u try create a new post and does this work? wordpress.stackexchange.com/questions/306058/… the comment from Harshal Solanki maybe helps you, its rewriting on fly
    – StefanBD
    Apr 12 at 16:34
  • ah sorry was the wrong answer but after wpexplorer.com/change-permalinks-wordpress my way with change it over the permlink option should work and for me it does in the past
    – StefanBD
    Apr 12 at 16:41

1 Answer 1

Reset to default
0

If you are using MySQL 8 and above you can use REGEXP_REPLACE,

e.i.

UPDATE `wp_posts` 
SET post_content=REGEXP_REPLACE(post_content, 'domain.tld\/[0-9]{4}\/[0-9]{2}\/[0-9]{2}\/', 'domain.tld/') 
WHERE post_content REGEXP 'domain.tld\/[0-9]{4}\/[0-9]{2}\/[0-9]{2}\/';

this would look for exact match domain.tld/{4 digit int}/{2 digit int}/{2 digit int}/ then replace it with domain.tld/

so it will replace something like domain.tld/2000/01/01/my-cool-post/ to domain.tld/my-cool-post/

Another option if you cannot do it via direct SQL query;

you can query and loop through each post, then update the post_content using preg_replace

e.i

$new_post_content = preg_replace('/domain.tld\/\d{4}\/\d{2}\/\d{2}\//', 'domain.tld/', $old_post_content );

This would do the same exact match and replace as the sql query

P.S. DO NOT forget to backup your database before doing anything, or play in a local/staging environment

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.