By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,246 Members | 1,451 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,246 IT Pros & Developers. It's quick & easy.

Replace and Update

P: n/a
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

Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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

Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.