CODEWITHSUNDEEP
sqlmysql
titel
titel

Reputation: 3502

SQL to search and replace in mySQL

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

Upvotes: 4

Views: 5029

Answers (4)

HLGEM
HLGEM

Reputation: 96552

If you have multiple replaces of different 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 one-time 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 MySQL, the code is what I would use in SQL Server, my SQL syntax may be different but the technique would be similar.

Upvotes: 1

Reputation:

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

Upvotes: 0

Yuliy
Yuliy

Reputation: 17718

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.

Upvotes: 6

j_random_hacker
j_random_hacker

Reputation: 51226

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

Upvotes: 1

Related Questions