Replace all fields in MySQL

Question

I need to replace some chars in the columns of a table, by using the REPLACE command.
I know that the REPLACE command needs a column name, then the text to change (in the following example, the 'a' char) and the new text (in the following case, the 'e' char).

UPDATE my_table SET my_column = REPLACE (my_column,'a','e' );

So that executing this command will change all the 'a' occurrences in the my_column column of the my_table table with the 'e' char.

But what if i need to execute the REPLACE command for every column and not just for one? Is this possible?

Thanks

Solution 1

Use the following SQL query to generate the SQL queries that you need to replace a value in all columns.

select concat(
       'UPDATE my_table SET ',
       column_name,
       ' = REPLACE(', column_name, ', ''a'', ''e'');')
from information_schema.columns
where table_name = 'my_table';

After executing this SQL query simply run all queries to replace all values.

---

Untested after some googling

Create a stored procedure with a core like this. It can accept the name of the table, the value to find and the value to replace for.

The main idea is to use:

1. prepared statements for dynamic SQL execution;
2. cursors to iterate over all columns of a table.

See partial code (untested) below.

DECLARE done INT DEFAULT 0;
DECLARE cur1 CURSOR FOR
    SELECT column_name FROM information_schema.columns
    WHERE table_name = 'my_table';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN cur1;
REPEAT
    SET s = concat(
       'UPDATE my_table SET ',
       column_name,
       ' = REPLACE(', column_name, ', ''a'', ''e'');');
    PREPARE stmt2 FROM s;
    EXECUTE stmt2;
    FETCH cur1 INTO a;
UNTIL done END REPEAT;
CLOSE cur1;

Solution 2

I made one minor change:

select concat(
   'UPDATE ', table_name, ' SET ',
   column_name,
   ' = REPLACE(', column_name, ', ''OLDTEXT'', ''NEWTEXT'');')
from information_schema.columns
where table_name = 'TABLENAME';

Which will use the variable for TABLENAME (just a bit less typing) - so you only need to replace the stuff in caps.

Also, I didn't understand at first, but this will only output a list of SQL Queries which you then have to execute to actually replace the code. Hope this helps...

Solution 3

This will do the trick with some PHP since MySQL stuff often includes PHP. Tested and working :)

<?php

        $host = 'localhost';
        $user = 'root';
        $pass = 'yourpass';
        $db = 'your_database_name';

        $connection = mysql_connect($host, $user, $pass);
        mysql_select_db($db);

        $thisword = "this one should be";
        $shouldbe = "like this";
        $thistable = "your_table_name";

        MySQL_replace_all($thisword, $shouldbe, $thistable);

        function MySQL_replace_all($thisword,$shouldbe,$thistable){
            $cnamnes = "SHOW columns FROM " . $thistable;
            $result = mysql_query($cnamnes);
            while($columnname = mysql_fetch_row($result)){
                $replace_SQL = "UPDATE $thistable SET ". $columnname[0] ." = REPLACE(". $columnname[0] .",'". $thisword ."', '". $shouldbe ."');";
                echo $replace_SQL . "<br>";
                mysql_query($replace_SQL);
            }
    }

?>

Comments

[OMG Ponies]

You'll want to check in the WHERE to be sure you're attempting to run text replacement on only columns that use text based data types ;). And this could be done using MySQL Prepared Statements (dynamic SQL)...

[Mark]

I think, but maybe i'm wrong, that the suggested query needs one more comma at position (4, 36). Now it generates UPDATE my_table SET my_column = REPLACE(my_column'ù', 'ù'); instead of UPDATE my_table SET my_column = REPLACE(my_column,'ù', 'ù');

[John]

Upvoted for a couple reasons. First while there is probably some wildly convoluted way to do this you've still managed to post something that WILL work. Secondly you went to the effort of providing a full PHP file to do this which is helpful to those just starting out.

[John]

@carl_lingberg You're welcome, playlist 2.8? If so I made that song.

[K. Kilian Lindberg]

Sure, it was playlist 2.8