0

Unlike with Joomla, one can't just search and replace a string in WordPress database because of serialized data. I am not sure what serialized data is, but it makes my life more complicated.

Is there a search and replace software for windows that can be used for this?

Can one use something like a PowerGrep or a similar grep tool for this?

If yes, can you please describe the process?

  • It might be more helpful if you specify what (and why) you need to search and replace. There are 'search and replace' plugins (I like "Better Search and Replace") that will do this for you. Although you can do it with SQL commands, it can be dangerous if you are not careful. Backups first, of course. – Rick Hellewell Mar 8 at 4:37
  • I know that there are plugins, but I'd like to have an option that works on Windows. I search-replace site domain (e.g. from test.example.com to example.com or from test.com to domain.com) or parts of URL (from example.com/page-title-2018 to example.com/page-title-2019). I can't remember if I needed this for something else. – Mali Veseljko Mar 8 at 4:48
0

Give WPCLI's search-replace command a try:
https://developer.wordpress.org/cli/commands/search-replace/

WPCLI is cross-platform and the search-replace command handles serialized data.

  • Thank you for your reply. Can you please explain how is this used? Can I run for example "wp search-replace 'example.dev' 'example.com' " in Windows command line, or in PHPMyAdmin or ...? – Mali Veseljko Mar 9 at 21:55
  • It used through the command line pretty much how you mentioned. Just make sure you cd into the WordPress directory first. – Atanas Angelov Mar 9 at 22:31
  • This didn't help me at all? Can you please explain if this works with Windows or just Linux? How does this command know in which database to do the search/replace? – Mali Veseljko Mar 10 at 0:16
  • I already mentioned it is cross platform in the original answer. There are multiple installation options as well. The command knows which database etc. to use because it reads your local configuration which is why I mentioned that you should cd into the WordPress directory first. In short: 1. Install WP CLI using the .phar or composer, for example 2. cd using your terminal of choice into the WordPress project's directory. 3. Run wp search-replace according to your needs – Atanas Angelov Mar 10 at 10:35
0

I do highly recommend using WP-CLI (as Atanas Angelov suggested) to search and replace WordPress databases. But your question was how to do it to a SQL file. I have written a PHP function that handles serialized data while searching and replacing SQL. In my experience it has worked very well. If you have feedback let me know, but feel free to use it.

You asked for a program that does this, it wouldn't be too hard to turn this function into a CLI tool or as a small graphical application with whatever programming language you prefer. All you need to do to run it like this is to place it in a PHP file along with a call to the function at the bottom of the file like this:

snr("http://example.com", "https://example.com", "/path/to/original.sql", "/path/to/replaced.sql");

You can execute the file with php -e /path/to/whatever-you-name-the-file.php

I wouldn't just leave this file sitting in a publicly accessible directory on a server, but it works well.

Apart from this, there is a popular "interconnectit" script (https://github.com/interconnectit/Search-Replace-DB) that you can upload to a server and use from your browser to search and replace a database. There are also several WordPress plugins in the WP repo for searching and replacing databases. One is https://wordpress.org/plugins/better-search-replace/

    function snr($search, $replace, $inputfile, $outputfile){
    $sql = file_get_contents($inputfile);
    $sql1 = str_replace($search,$replace,$sql);
    file_put_contents($outputfile,$sql1);
    $serstrings = preg_split("/(?<=[{;])s:/",$sql1);

    foreach($serstrings as $i=>$serstring) {
        if (!!strpos($serstring, $replace)){
        $justString = str_replace("\\","",str_replace("\\\\","j",explode('\\";',explode(':\\"',$serstring)[1])[0]));
        $correct = strlen($justString);
        $serstrings[$i] = preg_replace('/^\d+/',$correct, $serstrings[$i]);
         } 
    }
    file_put_contents($outputfile,implode("s:",$serstrings));
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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