regex SQL command for bulk URL update via wp-cli

  • pro99

    (@pro99)


    9 years, 1 month ago

    We made URL permalink changes to WordPress sites, removing a no longer needed date in URLs and keeping just the domain, subfolder and postname.

    from /%year%/%monthnum%/%day%/%postname%/ to /%postname%/

    In wordpress we made the following regex redirect: Source: /(\d*)/(\d*)/(\d*)/(.*), target: $4. While the 301 redirect is working perfectly we have hundreds of URL references in post contents that we’d like to update at once with WP-CLI. Does anyone know how to create a regular expression SQL command for this in WP-CLI to remove the date from URLs, making all internal links current again?

Viewing 1 replies (of 1 total)
  • Moderator keesiemeijer

    (@keesiemeijer)

    9 years, 1 month ago

    Hi pro99

    Before you do this make a backup of your database.
    https://codex.ww.wp.xz.cn/Backing_Up_Your_Database
    And test it works locally
    https://premium.wpmudev.org/blog/testing-wordpress-backups/

    This is super important as many things can go wrong if you do a search replace on a database.

    Because wp-cli doesn’t show you what gets replaced it might be a good idea to use this tool to see exactly what is replaced.
    https://interconnectit.com/products/search-and-replace-for-wordpress-databases/

    Here is a simple regex to search for a domain http://test.dev with a date format followed by a forward slash. It captures what comes after that.
    Change the domain, check “regex” and put this in the “search for” field

    
http:\/\/test\.dev\/\d{4}\/\d{2}\/\d{2}\/(.*?)

    Put this in the “replace with” field

    
http://test.dev/$1

    Click “dry run” and it shows you exactly what will be replaced.

    If your satisfied with the results you can use the same regex and do a dry run with wp-cli. Change the domain in the following.

    
wp search-replace 'http:\/\/test\.dev\/\d{4}\/\d{2}\/\d{2}\/(.*?)' 'http://test.dev/\1' wp_posts --include-columns=post_content --skip-columns=guid --regex --dry-run

    After you run this it will show you how many replacements will be made.

    Note: Maybe you have to change the default table prefix wp_ in wp_posts. See your wp-config.php file or use this command to see what the prefix is for the posts table.

    
wp db tables

    Remove --dry-run to make the replacements.

    • This reply was modified 9 years, 1 month ago by keesiemeijer. Reason: add table prefix info
    • This reply was modified 9 years, 1 month ago by keesiemeijer.
Viewing 1 replies (of 1 total)

The topic ‘regex SQL command for bulk URL update via wp-cli’ is closed to new replies.