Query on end of email address

david glass

Hi - Somewhat related to GDPR, I'm trying to find a better way to query based on email address suffix.  Perhaps I'm overlooking something, but right now to find an email address from an EU country, I have a query that looks at email address and CONTAINS %@%.fr (or replace the .fr with the other EU countries).  The challenge is that since there's no choice to say email address ENDS WITH something, the query I'm using also returns emails like:  JohnSmith@kentucky.frankfort.edu.us


I'd love to hear if there are any suggestions.


Thanks,

David

Jeremy Reynolds

david glass:

Hi - Somewhat related to GDPR, I'm trying to find a better way to query based on email address suffix.  Perhaps I'm overlooking something, but right now to find an email address from an EU country, I have a query that looks at email address and CONTAINS %@%.fr (or replace the .fr with the other EU countries).  The challenge is that since there's no choice to say email address ENDS WITH something, the query I'm using also returns emails like:  JohnSmith@kentucky.frankfort.edu.us


I'd love to hear if there are any suggestions.


Thanks,

David

Depends on what you're trying to accomplish. If it's to clean up data, probably a lot easier to do in excel. You could always push the results back into LO by adding the constituents to a group or by adding a custom parameter to their record.


If you really needed to do it on-the-fly, you could use S130 to extract the final characters of the address. Haven't tested it on a live server, but something like...

[[E130: [[T8:[[S1:primary_email]]]] dup dup length swap "." lastindexof swap substring]]

...should return everything from the last dot onward.


If you go that route, you could probably compare a string of domains by adding a couple random characters to the end...

[[E130:

.fr_.eu_.uk_.europe_.whatever_.sk_

[[T8:[[S1:primary_email]]]] dup dup length swap "." lastindexof swap substring

"_" concat indexof -1 >

1 "EUROPEAN" replaceall

0 "NON-EUROPEAN" replaceall

]]

...you could add anything you want to that test, just make it one long string where each TLD is followed by an underscore.