Oliver Spiesshofer wrote:
I would like to replace all strings in a table with regexp:
the strings contain the substring "-na", and I would like to replace the
whole table field with the original content but without the substring "-
na".
I know already how to find the lines that contain the "-na" with LIKE
and/or REGEXP, but i dont know how to replace it.
I am not aware of an update statement that could achieve this in one
query.
The typical solution for this kind of job is to write a routine in
your api language. Here is a perl approach:
----------------------------------------------------
#!/usr/bin/perl
use DBI;
# walk through the rows of mytable
$readdb=DBI->connect("DBI:mysql:your_db:your.host.com","userna me","pass");
$readquery=$readdb->prepare("SELECT ID,myfield FROM mytable");
$readquery->execute;
$readnumrows = $readquery->rows;
# for each row found, read the value to be replaced
# and perform an update query
while (@readarray = $readquery->fetchrow_array)
{
# assign vars
$ID=$readarray[0];
$newvalue=$readarray[1];
# perform the regexp in perl
$newvalue=~s/\-na//i;
# update this row
$updatedb=DBI->connect("DBI:mysql:your_db:your.host.com","userna me","pass");
$updatequery=$updatedb->prepare("UPDATE mytable SET
myfield='$newvalue' WHERE ID='$ID'");
$updatequery->execute;
# end of this query
$updatequery->finish;
$updatedb->disconnect;
}
# end of main read query
$readquery->finish;
$readdb->disconnect;
# report
print "done.\n";
----------------------------------------------------
If you are not a perl man, the same routine can be made in php, vb...
Hope this could be useful.
Bart
