Find and Replace text in the entire table using a MySQL query

Question

Usually I use manual find to replace text in a MySQL database using phpmyadmin  I m tired of it now  how can I run a query to find and replace a text with new text in the entire table in phpmyadmin    Example  find keyword domain com  replace with www domain com

User · Answer

In the case of sentences with uppercase - lowercase letters   We can use BINARY REPACE  UPDATE  table 1   SET   field 1     BINARY REPLACE  field 1    find string    replace string

User · Answer

Another option is to generate the statements for each column in the database   SELECT CONCAT       update    table name          set    column name      replace    column name      www oldDomain com      www newDomain com        AS statement FROM information schema columns WHERE table schema    mySchema  AND table name LIKE  yourPrefix       This should generate a list of update statements that you can then execute

User · Answer

UPDATE table SET field   replace field  text needs to be replaced  text required     Like for example  if I want to replace all occurrences of John by Mark I will use below    UPDATE student SET student name   replace student name   John    Mark

User · Answer

If you are positive that none of the fields to be updated are serialized  the solutions above will work well     However  if any of the fields that need updating contain serialized data  an SQL Query or a simple search replace on a dump file  will break serialization  unless the replaced string has exactly the same number of characters as the searched string      To be sure  a  serialized  field looks like this     a 1  s 13  administrator  b 1       The number of characters in the relevant data is encoded as part of the data  Serialization is a way to convert  objects  into a format easily stored in a database  or to easily transport object data between different languages  Here is an explanation of different methods used to serialize object data  and why you might want to do so  and here is a WordPress-centric post  Serialized Data  What Does That Mean And Why is it so Important  in plain language   It would be amazing if MySQL had some built in tool to handle serialized data automatically  but it does not  and since there are different serialization formats  it would not even make sense for it to do so    wp-cli Some of the answers above seemed specific to WordPress databases  which serializes much of its data   WordPress offers a command line tool  wp search-replace  that does handle serialization  A basic command would be         wp search-replace  an-old-string   a-new-string  --dry-run   However  WordPress emphasizes that the guid should never be changed  so it recommends skipping that column  It also suggests that often times you ll want to skip the wp users table  Here s what that would look like     wp search-replace  https   old-domain com   https   shiney-new-domain com  --skip-columns guid --skip-tables wp users --dry-run    Note  I added the --dry-run flag so a copy-paste won t automatically ruin anyone s database   After you re sure the script does what you want  run it again without that flag   Plugins If you are using WordPress  there are also many free and commercial plugins available that offer a gui interface to do the same  packaged with many additional features   Interconnect it php script Interconnect it offers a php script to handle serialized data  Safe Search and Replace tool  It was created for use on WordPress sites  but it looks like it can be used on any database serialized by PHP  Many companies  including WordPress itself  recommends this tool  Instructions here  about 3 4 down the page

User · Answer

the best you export it as sql file and open it with editor such as visual studio code and find and repalace your words  i replace in 1 gig file sql in 1 minutes for 16 word that total is 14600 word  its the best way  and after replace it save and import it again  do not forget compress it with zip for import

User · Answer

UPDATE   MySQL Table   SET   MySQL Table Column    REPLACE  MySQL Table Column    oldString    newString   WHERE   MySQL Table Column  LIKE  oldString

User · Answer

phpMyAdmin includes a neat find-and-replace tool   Select the table  then hit Search   Find and replace  This query took about a minute and successfully replaced several thousand instances of oldurl ext with the newurl ext within Column post content    Best thing about this method   You get to check every match before committing   N B  I am using phpMyAdmin 4 9 0 1

User · Answer

The easiest way I have found is to dump the database to a text file  run a sed command to do the replace  and reload the database back into MySQL   All commands below are bash on Linux   Dump database to text file  mysqldump -u user -p databasename  gt    db sql   Run sed command to find replace target string  sed -i  s oldString newString g    db sql   Reload the database into MySQL  mysql -u user -p databasename  lt    db sql   Easy peasy

User · Answer

I believe  swapnesh  answer to be the best   Unfortunately I couldn t execute it in phpMyAdmin  4 5 0 2  who although illogical  and tried several things  it kept saying that a new statement was found and that no delimiter was found     Thus I came with the following solution that might be usefull if you exeprience the same issue and have no other access to the database than PMA     UPDATE  wp posts  AS  toUpdate    SELECT  ID  REPLACE  guid   http   old tld   http   new tld   AS  guid    FROM  wp posts  WHERE  guid  LIKE  http   old tld    AS  updated  SET  toUpdate   guid   updated   guid  WHERE  toUpdate   ID   updated   ID     To test the expected result you may want to use    SELECT  toUpdate   guid  AS  old guid   updated   guid  AS  new guid  FROM  wp posts  AS  toUpdate    SELECT  ID  REPLACE  guid   http   old tld   http   new tld   AS  guid   FROM  wp posts  WHERE  guid  LIKE  http   old tld    AS  updated  WHERE  toUpdate   ID   updated   ID

User · Answer

For a single table update      UPDATE  table name   SET  field name    replace same field name   unwanted text    wanted text     From multiple tables-   If you want to edit from all tables  best way is to take the dump and then find replace and upload it back

User · Answer

Generate change SQL queries  FAST  mysql -e  quot SELECT CONCAT   update    table name     set    column name      replace    column name      www oldsite com      www newsite com        AS statement FROM information schema columns WHERE table name LIKE  wp    quot  -u root -p your db name here  gt  upgrade script sql Remove any garbage at the start of the file  I had some  nano upgrade script sql Run generated script with --force options to skip errors   SLOW - grab a coffee if big DB  mysql -u root -p your db name here --force  lt  upgrade script sql

User · Answer

Put this in a php file and run it and it should do what you want it to do      Connect to your MySQL database   hostname    localhost    username    db username    password    db password    database    db name    mysql connect  hostname   username   password       The find and replace strings   find    find this text    replace    replace with this text     loop   mysql query       SELECT         concat  UPDATE   table schema     table name    SET   column name    replace   column name        find         replace        AS s     FROM         information schema columns     WHERE         table schema      database     or die   Cant loop through dbfields      mysql error      while   query   mysql fetch assoc  loop             mysql query  query  s

User · Answer

Running an SQL query in PHPmyadmin to find and replace text in all wordpress blog posts  such as finding mysite com wordpress and replacing that with mysite com news Table in this example is tj posts  UPDATE  tj posts  SET  post content    replace post content   mysite com wordpress    mysite com news

[mysql] Find and Replace text in the entire table using a MySQL query

Examples related to mysql

Examples related to phpmyadmin