Find and Replace text in all table using mysql query

Question

I have a database with name test having two tables

1. sample1(name, quote)
2. sample2(name, quote)

I need a replace function or procedure that does the following

1. Find columns with String data type (Text,varchar,char,etc) in all tables within test database
2. Look for a value in that column and replace it with a new value

I have found an stored procedure in this post but I don't understand how it works.

is there anyway to achieve this or adopt the stored procedure in the linked post for my use?

Answer 1

MySQL's replace function should come handy to replace the text you need:

update $table set column = replace(column, 'old_text', new_text') where column='old_text' and <other_conditions>;

Now, as you talk of replace in all tables, that's something not possible in single query. Though you can create a routine to do so! For the sample you can refer a procedure which is finding a string in all tables of all databases.

• (Though been already mentioned in comments) table_schema is a column name of information_schema.tables table which represents the database name.