Geek Answers Handbook

SQL for search and replace in mySQL

In the process of fixing a poorly imported database, problems arise due to the use of incorrect database encoding, or something like that.

In any case, returning to my question to fix these problems, I use the request of this form:

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

Thus, if there are several columns in the table in which I work, I have to call this query for each column. And also, since not only one pair of things is required to start the search and replace, I must also call a query for each of these pairs.

So, as you can imagine, I run dozens of queries to fix one table.

I was wondering if there is a way to combine multiple find and replace in one query, for example, say, look for this set of things, and if it is found, replace the corresponding pair from this other set of things.

Or, if there was a way to request the form shown above, run somehow recursively, for each column of the table, regardless of their name or number.

Thanks in advance for your support, Titel

+4
source share
4 answers

Try to deal with each of them separately:

If the set of substitutions is the same for each column in each table that you need to make (or there are only a few templates), consider creating a custom function that takes varchar and returns varchar, which simply calls replace(replace(@input,'search1','replace1'),'search2','replace2') nested accordingly.

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 similar for each column in each table, I would think it would be easier to write code that extracts a list of columns and generates queries to execute from this.

+6
source

I don’t know how to automatically start searching and replacing in each column, however, the problem with several search and replace pairs in one UPDATE query is easily solved by nesting calls in replace() :

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

If there are several notes of another text in one field, I recommend creating a table with the current values ​​and what you want them to be replaced. (Perhaps this is a temporary table, if it is a one-time transaction, and if not, make it a permanent table.) Then join this table and update.

Sort of:

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

Sorry, I didn’t notice that this is MySQL, the code is what I will use in SQL Server, my SQL syntax may be different, but the method will be similar.

+1
source

I wrote a stored procedure that does this. I use this for every level of the database, although it would be easy to distract it so that it works globally across the server.

I would just insert this inline, but it would seem that I am too tight to understand how to use the markdown transaction, so the code is here:

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

0
source

More articles:


All Articles