469,579 Members | 1,239 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,579 developers. It's quick & easy.

replacing string with regexp

Hi,

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.

any help possible here?

thanks

Oliver

Jul 19 '05 #1
6 10257
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
Jul 19 '05 #2
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
Jul 19 '05 #3
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
Jul 19 '05 #4
ba**@nijlen.com (Bart Van der Donck) wrote in
news:b5**************************@posting.google.c om:
Oliver Spiesshofer wrote:
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:

If you are not a perl man, the same routine can be made in php, vb...


thanks a lot. I was hoping to be able to use the %1... syntax of the regexp
also direclty in the update statement to reflect the results. what a pity
it does not work.

I will do it in PHP then, thanx!

Oliver

Jul 19 '05 #5
ba**@nijlen.com (Bart Van der Donck) wrote in
news:b5**************************@posting.google.c om:
Oliver Spiesshofer wrote:
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:

If you are not a perl man, the same routine can be made in php, vb...


thanks a lot. I was hoping to be able to use the %1... syntax of the regexp
also direclty in the update statement to reflect the results. what a pity
it does not work.

I will do it in PHP then, thanx!

Oliver

Jul 19 '05 #6
ba**@nijlen.com (Bart Van der Donck) wrote in
news:b5**************************@posting.google.c om:
Oliver Spiesshofer wrote:
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:

If you are not a perl man, the same routine can be made in php, vb...


thanks a lot. I was hoping to be able to use the %1... syntax of the regexp
also direclty in the update statement to reflect the results. what a pity
it does not work.

I will do it in PHP then, thanx!

Oliver

Jul 19 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Sorby | last post: by
10 posts views Thread by Anand Pillai | last post: by
reply views Thread by Oliver Spiesshofer | last post: by
3 posts views Thread by Arjen | last post: by
14 posts views Thread by Adnan Siddiqi | last post: by
reply views Thread by deathtospam | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.