I am trying to look for a way to replace all occurrences of a particular pattern in my database across many tables and columns, for this I need to create some way to do this, it does not need to be done by a script, just some SQL code that will do this.
For example, I want to replace all occurrences of 'v2' with 'www' but have no idea how to do this.
I am not looking for a tutorial, just a bit of guidance on what to do and how to script the SQL needed.
How do I go about doing this?
Just to guide you in a direction. You can use the replace function:
UPDATE MyTable
SET StringColumn = REPLACE (StringColumn, 'SearchForThis', 'ReplaceWithThis')
WHERE SomeOtherColumn LIKE '%PATTERN%';
In an earlier post there is more information: How can I use mySQL replace() to replace strings in multiple records?
You can use a select CONCAT(...) from information_schema.columns to generate an update query for every table-column combination, where ... is a combination of the strings used in an update query, and column names of the information_schema.columns database.
For example:
select CONCAT("UPDATE ", TABLE_NAME, " SET ", COLUMN_NAME, "=REPLACE(",COLUMN_NAME,"'[string-to-find]'","'[string-that-will-replace-it]'",");") FROM information_schema.columns where table_schema = 'your_db';
COLUMN_NAME and TABLE_NAME are columns in the information_schema.columns table, as documented by MySQL
The above query should make the result set:
UPDATE table1 SET field1 = replace(field1,'[string-to-find]','[string-that-will-replace-it]');
UPDATE table1 SET field2 = replace(field2,'[string-to-find]','[string-that-will-replace-it]');
UPDATE table2 SET field3 = replace(field3,'[string-to-find]','[string-that-will-replace-it]');
...
You could output the results of the SELECT statement to a file, which then becomes a script to execute. Alternatively, if you use phpadmin or any other programming language as an interface, you can cycle through the results set, and execute the value of each row in the results set.
I got the idea from MikeW's answer here, about selecting all rows where a data value exists, and from some other stack overflow answers that I have now lost track of, sadly (sorry to the original writers)
To be honest, I think this question may be a duplicate of this, in addition to this though...
update TABLE_NAME set FIELD_NAME = replace(FIELD_NAME, 'Text to search, 'Text to replace it with');
