Help in Update Statement

[vr76413]

Hello everyone..

I need help here in Writing this update statement on a Column 'emailaddr_'. One of our Clients have changed their emai Domain from say abc.com to xyz.com. I need to update all the Rows that abc.com to xyz.com.


For eg.

Source Table Column
--------------------
john@abc.com
bob@abc.com


target Table
------------
john@xyz.com
bob@xyz.com


I have like 950 Records whose email domain needs to be changed.

Can anyone help me here in Writing a sq ?..

Any help is appreciated

Ron

[LKBrwn_DBA]

Look (http://www.jargon.net/jargonfile/r/RTFM.html) at the REPLACE (http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/functions102a.htm#SQLRF00697) function in sql. :rolleyes:

[dharma]

UPDATE table_name set column_name=replace(column_name,'abc.com','xyz.com')

Assuming that abc.com comes only as the domain name.

HTH.

[gamyers]

And that there isn't a domain with fred_abc.com
Try
UPDATE table_name set column_name=replace(column_name,'@abc.com','@xyz.com')
where substr(column_name,-1*length(@abc.com')) ='@abc.com'

[vr76413]

thanks a lot guys....appreciate it.

Ron

[WilliamR]

where substr(column_name,-1*length(@abc.com')) ='@abc.com'I think that is the same thing as

WHERE column_name LIKE '%@abc.com'

[gamyers]

The LIKE should be fine in this context.
Generally, I prefer to use an equals because there's the potential for the contents of a variable to include LIKE wildcard characters (_ and %) which can confuse the result
EG
SELECT * from table(sys.dbms_debug_vc2coll ('@test.com','@tast.com','@t_st.com'))
where column_value like '%@t_st.com'

However, I did a quck check and these characters shouldn't be in the domain name so the issue should not arise