Parsing e-mail addresses

[SgtSaunders69]

I need to parse (separate out) the parts the field containing e-mail address for display in a select query..



In the results I need to take a field 'emailaddress@hotmail.com' and display it a calculated expression as 'emailaddress' in one field and as "hotmail.com" in another field... so I can filter on the email provider...essentially removing everything to the right or left of the ampersand.. including the ampersand.

My returned data should look like this..
Field1 Field2 Field2
emailaddress@hotmail.com emailaddress hotmail.com

I don't want to change the source data.. just parse for viewing / filtering purposes. I tried the code...

StaffStu: Right(Trim([EmailAddress]),Len(Trim([EmailAddress]))@InStr(1,[Emailaddress],""))

But it errors on the Ampersand.

Any help would be appreciated!

[ssanfu]

Try this:

URL_Front: Left([Emailaddress],InStr([emailaddress],"@")-1)
URL_Back: Right([emailaddress],InStr([emailaddress],"@")-2)

This is the query SQL:

SELECT Table1.emailaddress, Left([Emailaddress],InStr([emailaddress],"@")-1) AS URL_Front, Right([emailaddress],InStr([emailaddress],"@")-2) AS URL_Back
FROM Table1;

BTW,
This is the "at" sign-> @
This is the Ampersand -> &

[SgtSaunders69]

The first line you suggested (URL_Front: Left([Emailaddress],InStr([emailaddress],"@")-1)) works fine. The second line which should return everything after the @ sign returns varying numbers of the characters to the right... not consistent from record to record.

Try this:

URL_Front: Left([Emailaddress],InStr([emailaddress],"@")-1)
URL_Back: Right([emailaddress],InStr([emailaddress],"@")-2)

This is the query SQL:

SELECT Table1.emailaddress, Left([Emailaddress],InStr([emailaddress],"@")-1) AS URL_Front, Right([emailaddress],InStr([emailaddress],"@")-2) AS URL_Back
FROM Table1;

BTW,
This is the "at" sign-> @
This is the Ampersand -> &

[ssanfu]

Sorry... had my Doh!! hat on


OK, I tested with 3 addresses.....

URL_Front: Left([Emailaddress],InStr([emailaddress],"@")-1)
URL_Back: Right([emailaddress],Len([emailaddress])-InStr([emailaddress],"@"))

SELECT Table1.emailaddress, Left([Emailaddress],InStr([emailaddress],"@")-1) AS URL_Front, Right([emailaddress],Len([emailaddress])-InStr([emailaddress],"@")) AS URL_Back
FROM Table1;

[SgtSaunders69]

Thanks... I also discovered the "MID" command works too.

Again.. thanks!