SQL to search and replace in mySQL

Question

In the process of fixing a poorly imported database with issues caused by using the wrong database encoding, or something like that.

Anyways, coming back to my question, in order to fix this issues I'm using a query of this form:

UPDATE table_name SET field_name = replace(field_name,’search_text’,'replace_text’);

And thus, if the table I'm working on has multiple columns I have to call this query for each of the columns. And also, as there is not only one pair of things to run the find and replace on I have to call the query for each of this pairs as well.

So as you can imagine, I end up running tens of queries just to fix one table.

What I was wondering is if there is a way of either combine multiple find and replaces in one query, like, lets say, look for this set of things, and if found, replace with the corresponding pair from this other set of things.

Or if there would be a way to make a query of the form I've shown above, to run somehow recursively, for each column of a table, regardless of their name or number.

Thank you in advance for your support, titel

Answer 1

Let's try and tackle each of these separately:

If the set of replacements is the same for every column in every table that you need to do this on (or there are only a couple patterns), consider creating a user-defined function that takes a varchar and returns a varchar that just calls replace(replace(@input,'search1','replace1'),'search2','replace2') nested as appropriate.

To update multiple columns at the same time you should be able to do UPDATE table_name SET field_name1 = replace(field_name1,...), field_name2 = replace(field_name2,...) or something similar.

As for running something like that for every column in every table, I'd think it would be easiest to write some code which fetches a list of columns and generates the queries to execute from that.

Answer 2

I don't know of a way to automatically run a search-and-replace on each column, however the problem of multiple pairs of search and replace terms in a single UPDATE query is easily solved by nesting calls to replace():

UPDATE table_name SET field_name =
    replace(
        replace(
            replace(
                field_name,
                'foo',
                'bar'
            ),
            'see',
            'what',
        ),
        'I',
        'mean?'
    )