Search and Replace in database: How to replace data in SQL dump file on Windows?

Question

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?

Answer 1

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.

Answer 2

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));
}