How to Replace Email Address 1

[TStriker]

Hi all,

I have a table with a lot of email addresses. One of the companies that we deal with just changed their name and therefore all of their people have new addresses.

The syntax remains the same i.e. First_Last@Company.com so what I would like to to is make a mass change to all rows in the table substituting the only the 'Company' portion of the address.

Thanks.

-Striker

 

[BJCooperIT]

update EMAIL_ADDRESS_MASTER
set    USER_EMAIL =
       replace(USER_EMAIL,'Company.com','NewName.com')
where  COMPANY_ID = .....;

Try in a select first to make sure your where clause is getting the right rows.

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

 

[TStriker]

Wow that was easy! I look like a dope for not knowing the replace function was available. I looked for a function like it in an old Oracle training book but didn't find it anywhere.

Anyway, I tried it on a single row and it worked like a charm.

Thanks Mr. Cooper, you are a gentleman and a scholar.

-Striker

 

[BJCooperIT]

You are quite welcome - however what I really am is a lady coder!


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

 

[TStriker]

Oops. Holy cow, I'm oh for two now. I thought about that possibility when I made the post but went with the odds... and missed.

-Striker

 

[SantaMufasa]

TS,

Just remember that "B" stands for "Barb" in addition to "Bob". And, as usual, Barb the Coder produces "good stuff", huh?

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[Beilstwh]

I would write the update a little different,

update EMAIL_ADDRESS_MASTER
set USER_EMAIL =
replace(USER_EMAIL,'@Company.com','@NewName.com');

If you don't include the @, then someone with smith@abcd.com and jones@bcd.com would both change.


Bill
Oracle DBA/Developer
New York State, USA

 

[BJCooperIT]

Good point!