Re: Search and Replace on whole database

[Jay Alverson]

Matthias Pospiech Wrote:
-------------------------------------------------------

> Just to understand these sql statements
>
> you create a table 'search_replace' in database
> 'test' and the column is called 'line'?

Yes.

Search & Replace is only done on one column: line.

If you have to Search and Replace on different columns,
then you need to do the same thing for EACH column.

You only need the SET line once. If say the same table had
two columns: line and line2 then it would look something
like...

update search_replace
set line = case when LOCATE("ü", line) > 0 then REPLACE(line,"ü","ü") end,
 line = case when LOCATE("ä", line) > 0 then REPLACE(line,"ä","ä") end,
 line = case when LOCATE("ß", line) > 0 then REPLACE(line,"ß","ß") end,
 line = case when LOCATE("ö", line) > 0 then REPLACE(line,"ö","ö") end,
 line = case when LOCATE("Ãœ", line) > 0 then REPLACE(line,"Ãœ","Ü") end,
 line = case when LOCATE("Ä", line) > 0 then REPLACE(line,"Ä","Ä") end,
 line = case when LOCATE("é", line) > 0 then REPLACE(line,"é","é") end,
 line = case when LOCATE("ã", line) > 0 then REPLACE(line,"ã","ã") end,  <-- note comma here

line2 = case when LOCATE("ü", line2) > 0 then REPLACE(line2,"ü","ü") end,
 line2 = case when LOCATE("ä", line2) > 0 then REPLACE(line2,"ä","ä") end,
 line2 = case when LOCATE("ß", line2) > 0 then REPLACE(line2,"ß","ß") end,
 line2 = case when LOCATE("ö", line2) > 0 then REPLACE(line2,"ö","ö") end,
 line2 = case when LOCATE("Ãœ", line2) > 0 then REPLACE(line2,"Ãœ","Ü") end,
 line2 = case when LOCATE("Ä", line2) > 0 then REPLACE(line2,"Ä","Ä") end,
 line2 = case when LOCATE("é", line2) > 0 then REPLACE(line2,"é","é") end,
 line2 = case when LOCATE("ã", line2) > 0 then REPLACE(line2,"ã","ã") end;  <- semi-colon on the last line of query...

Since you have to replace each item in each column.

I might be easier to export the table and do a replace outside the database
then import the new file.

MySQL comes with an .exe on the windows called REPLACE.exe that can do that
sort of thing on a text file.

Note: before doing any of this make sure you backup your existing data.

>

Thanks, Jay



Edited 1 time(s). Last edit at 08/14/2009 01:37PM by Jay Alverson.