470,631 Members | 2,027 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,631 developers. It's quick & easy.

Replace and Update

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
1 7930
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.

Similar topics

4 posts views Thread by Craig Keightley | last post: by
2 posts views Thread by Little PussyCat | last post: by
2 posts views Thread by bj daniels | last post: by
5 posts views Thread by Brian Blais | last post: by
6 posts views Thread by JackpipE | last post: by
6 posts views Thread by simon.robin.jackson | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.