Find and Replace Data in MySQL Database

Back to Blog
php mysql web development india

Find and Replace Data in MySQL Database

Here the below examples To find a string in a certain field and replace it with another string in MySql Database

Single Row :

update [table_name] set [field_name] = replace([field_name],'[string_to_find]','[string_to_replace]');

Multiple Row :

update [table_name] set [field_name_1] = replace([field_name_1],'[string_to_find_1]','[string_to_replace_1]'), [field_name_2] = replace([field_name_2],'[string_to_find_2]','[string_to_replace_2]') ....;

Multiple Conditional :

update [table_name] set [field_name] = replace([field_name],'[string_to_find]','[string_to_replace]') WHERE [condition];

In SQL Server :

UPDATE tbl_name
  SET column_name=REPLACE(column_name,'text_find','replace_this'); 

Replace using RegEx in MySQL:

Mysql does not support replace with Regular Expression (RegEx), but you can install lib_mysqludf_preg library to do something like.

UPDATE tbl_name
   SET col_name = PREG_REPLACE('regex_match', 'replace_value', col_name);

The point before run replace statement:

1. Assure your SQL statement before the run in the LIVE environment or keep the backup copy in safe place, There is no UNDO operation or statement available.

2. Changes will be affected/ visible immediately in the frontend.

Share this post

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back to Blog