Removing part of a data field

[prismtx]

I have a table which contains address information. Some email addresses are incorrect and have a "

http://www."

or a "[mailto:" in front of them. I can select them with the following:
select * from address
where lower(email) like ('

http://www.%')

or lower(email) like ('[mailto%')

I have about 1700 to update. Is there an easy way to strip those characters off of the front of the address? Or will I be better off to pass them to a perl script and update them there?

 

[Dagon]

Perhaps:

update address
set email = replace(email, '

http://www.',

null);

update address
set email = replace(email, '[mailto:', null);

 

[BJCooperIT]

Are you looking for something like:

update address
set    email = substr(email,5)
where  lower(email) like ('[URL unfurl="true"]www.%');[/URL]

update address
set    email = substr(email,8)
where  lower(email) like ('[mailto%');

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: www.EmuProductsPlus.com

 

[prismtx]

Thanks! Both methods work!

 

[karluk]

Perhaps both methods work with your data, but I would say that BJCooper's approach is more accurate. Dagon's update statements will replace "

http://www."

and "[mailto:" with null regardless of whether they are at the beginning of the string or someplace in the middle, such as "abc.

http://www.xyz".

Also, they don't work on strings that are in upper case, such as "

http://WWW.ABC.XYZ".

But your search criteria suggest that you only want to replace characters at the beginning of the string and without regard to whether the data is upper, lower, or mixed case.