correcting/replacing substrings 2

[roedelfroe]

Hi,

following problem:

I want to correct emai-addresses in a column:

Now:

Scott.Tiger@anycompanyintheworld.com

Should be:

Scott.Tiger@anycompanyworld.com

Any Ideas how to do this?

Thx in advance

Roland

 

[KenCunningham]

Doesn't:

Update <table>
set <column> = NewValue
where <column> = OldValue

do the trick?

 

[roedelfroe]

O.K., I see I have to a little bit more in detail:

I've got a table with about 18.000 email-addresses.

Table (now):

ID email Dept max.logon
1 scott.tiger@anycompanyintheworld.com 10 5
2 Joe.Brown@anycompanyintheworld.com 15 5
3 Peter.Mulder@anycompanyintheworld.com 15 3
4 John.Edwards@anycompanyintheworld.com 10 5

Table (should be):

ID email Dept max.logon
1 scott.tiger@anycompanyworld.com 10 5
2 Joe.Brown@anycompanyworld.com 15 5
3 Peter.Mulder@anycompanyworld.com 15 3
4 John.Edwards@anycompanyworld.com 10 5

As you can see, a simple update isn't possible. Up to the @ sign, the string varies, after the @ sign, I've got exactly one string (@anycompanyintheworld.com) which I could identify and change.

But obviously,

Update table set email = '%@anycompanyworld.com'
where email = '%@anycompanyintheworld.com'

woud'nt work.

So, again the question: Any ideas how to do this?

Thx in advance

Roland

 

[KenCunningham]

I see now. Try a keyword search for substring in this forum - there are plenty of examples.

 

[SantaMufasa]

Specifically,

update mytable set email =
    translate(email,'anycompanyintheworld','anycompanyworld')
    where email like '%anycompanyintheworld%';

Let us know if this resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[sem]

Follow Dave's advice but change translate to replace

Regards, Dima

http://seminihin.narod.ru

 

[roedelfroe]

Perfect! Thanks so much both of you!

Roland

 

[SantaMufasa]

Duh ! Thanks, Sem, for catching my stupidity.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.