Replace and Update

[dig314]

MSAccess linking to Oracle 8 tables with Microsoft ODBC for Oracle
driver

I need to change the email address of all users because our domain has
changed. Is there a way to do this with SQL and not an Edit Replace
from Access?

SELECT REPLACE ('domainOld.com', 'domainOld.com',
'domainNew.com') FROM User_Table

This query returns the correct information. How could I write an UDPATE
query to do the same thing ?

Thanks for your time.

Dig

[Ian Hinson]

Well, you could use Replace directly in an SQL update query
ie. UPDATE User_Table
SET EmailAddr = Replace([EmailAddr], "olddomain.com", "newdomain.com");

But.. it would fail if any user has a Null email address, since Replace
cannot accept a Null parameter.

Safer to write a user-defined function eg.

Public Function ReplaceDomain(ByVal emailaddr as Variant) as Variant
If IsNull(emailaddr) Then
ReplaceDomain = Null
Else
ReplaceDomain = Replace(emailaddr, "olddomain.com", "newdomain.com")
End If
End Function

then use ReplaceDomain in the UPDATE query as shown above.

HTH,
Ian.
<di****@yahoo.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

MSAccess linking to Oracle 8 tables with Microsoft ODBC for Oracle
driver

I need to change the email address of all users because our domain has
changed. Is there a way to do this with SQL and not an Edit Replace
from Access?

SELECT REPLACE ('domainOld.com', 'domainOld.com',
'domainNew.com') FROM User_Table

This query returns the correct information. How could I write an UDPATE
query to do the same thing ?

Thanks for your time.

Dig