Replace all fields in MySQL

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开发者_StackOverflow社区 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

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;

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...

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

?>

You can't do what you want. If it was me, i'd take a list of column names and in my editor do a quick regex search and replace.

Find: (.+)

Replace: UPDATE my_table SET \1 = REPLACE (\1,'a','e' );