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

dba.stackexchange.com/questions/21147/query-to-find-and-replace-text-in-all-tables-and-fields-of-a-mysql-db — Foreever, Oct 15 '15 at 05:23

Jorge Ferreira · Accepted Answer · 2010-06-29T22:54:36.563

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:

prepared statements for dynamic SQL execution;
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;

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)... — OMG Ponies, Jun 29 '10 at 22:10
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,'Ã¹', 'ù'); — Mark, Jun 29 '10 at 22:26

score 7 · Answer 2 · answered Dec 06 '11 at 20:31

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

score 3 · Answer 3 · answered Mar 01 '13 at 15:08

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

?>

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, Feb 21 '14 at 20:43
@carl_lingberg You're welcome, playlist 2.8? If so I made that song. — John, Feb 24 '14 at 19:05

score 0 · Answer 4 · answered Jun 29 '10 at 22:00

0

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

answered Jun 29 '10 at 22:00

Byron Whitlock

52,691
28
123
168

Replace all fields in MySQL

4 Answers4

Linked

Related