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 :

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

Replace using RegEx in MySQL:

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

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

Point before run replace statement:

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

2. Changes will be affected/ visiable immediately in frontend.

Share this post

Leave a Reply

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

Back to Blog