SQL to search and replace in mySQL

Question

Hi guys,

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

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?'
    )

Answer 2

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 3

If you have multiple replaces of differnt text in the same field, I recommend that you create a table with the current values and what you want them replaced with. (Could be a temp table of some kind if this is a onetime deal, if not make it a permanent table) Then join to that table and do the update. Something like:

update t1 set field1 = t2.newvalue from table1 t1 join mycrossreferncetable t2 on t1.field1 = t2.oldvalue

Sorry didn't notice this is myslq, the code is what I would use in SQL Server, my sql syntax may be different but the techinque would be simliar.

Answer 4

I wrote a stored procedure that does this. I use this on a per database level, although it would be easy to abstract it to operate globally across a server.

I would just paste this inline, but it would seem that I'm too dense to figure out how to use the markdown deal, so the code is here:

http://www.anovasolutions.com/content/mysql-search-and-replace-stored-procedure