Find and Replace Query with Wildcards in Access

G

GoFlyersCS

I am trying to create a Find and Replace query with wildcards. For example,
I have e-mail addresses that have country codes in them such as
"[email protected]". I wish to get rid of the country codes in all of the
e-mail address so that they read simply "[email protected]". There are
different country codes in each e-mail address, so I need to use wildcards.
I have tried to use the Replace function with something like this:
UPDATE

SET
.email = Replace ,"@*.company.com","@company.com");

The query does not work with the wildcard "*" or any other wildcards I have
tried. Does anybody know how to create a Find and Replace query that will do
this?

Thanks.
 
J

John Vinson

I am trying to create a Find and Replace query with wildcards. For example,
I have e-mail addresses that have country codes in them such as
"[email protected]". I wish to get rid of the country codes in all of the
e-mail address so that they read simply "[email protected]". There are
different country codes in each e-mail address, so I need to use wildcards.
I have tried to use the Replace function with something like this:
UPDATE

SET
.email = Replace ,"@*.company.com","@company.com");

The query does not work with the wildcard "*" or any other wildcards I have
tried. Does anybody know how to create a Find and Replace query that will do
this?

Thanks.[/QUOTE]

The Replace() function won't serve your turn here I fear. You'll
probably need to use the string-handling functions. AIR CODE,
untested! Do this on a copy of the database first:

UPDATE [table]
SET [email] =
Left([email], InStr([email], "@") &
Mid([email], InStr([email], "@") + 3)
WHERE [email] LIKE "*@??.*";


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
Click to expand...
 
S

Skip

GoFlyersCS said:
I am trying to create a Find and Replace query with wildcards. For example,
I have e-mail addresses that have country codes in them such as
"[email protected]". I wish to get rid of the country codes in all of the
e-mail address so that they read simply "[email protected]". There are
different country codes in each e-mail address, so I need to use wildcards.
I have tried to use the Replace function with something like this:
UPDATE

SET
.email = Replace ,"@*.company.com","@company.com");

The query does not work with the wildcard "*" or any other wildcards I have
tried. Does anybody know how to create a Find and Replace query that will do
this?

Thanks.
[/QUOTE]

Don't know if this will help, but I have this note from some time in my
past.


For some reason, using the '*' character doesn't work. Instead, use the '%'
character!

i.e.:

SqlText = SqlText & " WHERE (((ItemDetail.Status) Like '%" &
Me.txtInput1.Value & "%')) "
Click to expand...
 
G

GoFlyersCS

John,

I get a "Syntax Error (missing operator)" when I try to save that query.
Any thoughts?

Thanks

John Vinson said:
I am trying to create a Find and Replace query with wildcards. For example,
I have e-mail addresses that have country codes in them such as
"[email protected]". I wish to get rid of the country codes in all of the
e-mail address so that they read simply "[email protected]". There are
different country codes in each e-mail address, so I need to use wildcards.
I have tried to use the Replace function with something like this:
UPDATE

SET
.email = Replace ,"@*.company.com","@company.com");

The query does not work with the wildcard "*" or any other wildcards I have
tried. Does anybody know how to create a Find and Replace query that will do
this?

Thanks.[/QUOTE]

The Replace() function won't serve your turn here I fear. You'll
probably need to use the string-handling functions. AIR CODE,
untested! Do this on a copy of the database first:

UPDATE [table]
SET [email] =
Left([email], InStr([email], "@") &
Mid([email], InStr([email], "@") + 3)
WHERE [email] LIKE "*@??.*";


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
[/QUOTE]
Click to expand...
Click to expand...
 
G

GoFlyersCS

Top