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 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.
Leave a Reply