Problem with INDEX_OF

rocketman

I'm trying to find records where users have tried to include two email addresses into a field only designed for one, The manual says that INDEX_OF can take an optional third parameter to denote the occurrence of a string but I keep getting an error saying can't be converted in my version of SQL (MySQL 5.7)

The search is FIND Members WHERE INDEX_OF('@',Members.EmailAddress,2)>0

So - in other words, if there is more than one @ in the email address string then bomb them out. Is there another way of achieving this? OR alternatively is there a syntax that will allow AIM to send to more than one address - something like Me@here.com;Me2@here.com

Am asking here because I don't run a mail server on my Dev server and my production server's mail server is locked down to prevent relaying and I don't want to play around in a live database

BobK

Rocketman,

I do not think you can use INDEX_OF as part of a query.

There might be better ways to do what you want, but here is one way:
Use the EXE_SQL function like

EXEC_SQL `SELECT * FROM Members WHERE EmailAddress LIKE '%@%@%'` RETURN Members

If you are using the default AwareIM database, you will need to add your BSV name, followed by the under score (_), with the first Members like:

EXEC_SQL `SELECT * FROM BSV_Members WHERE EmailAddress LIKE '%@%@%'` RETURN Members

This will put Members with multiple email addresses into Context just like a FIND statement.

PointsWell

rocketman wrote
The search is FIND Members WHERE INDEX_OF('@',Members.EmailAddress,2)>0

So - in other words, if there is more than one @ in the email address string then bomb them out. Is there another way of achieving this? OR alternatively is there a syntax that will allow AIM to send to more than one address - something like Me@here.com;Me2@here.com

If you have users adding multiple email addresses then they must be using some delimiter to separate them, like SPACE, ";" , "," or some other character that is invalid for an email address.

Would it not be easier to look for these symbols in your query?

Presumably this is a recovery exercise to fix rather than an ongoing measure, as you'd be better to manage this via business rules or field validation at BO creation

rocketman

Presumably this is a recovery exercise to fix rather than an ongoing measure, as you'd be better to manage this via business rules or field validation at BO creation

Correct - and in fact I've done just that and found lots of errors including a .co. and some with carriage returns at the end. The actual attribute is meant to be formatted as e-mail (It's the standard AIM EmailAddress attribute) so I'm failing to understand why its not rejecting these as a matter of course.

PointsWell

Got it - if this is a once off (and you don't have 10 million records, this might be better done via an export or if there are millions of records via an ETL tool like Pentaho Data Integration. There is a paid and a community version available.

Personally, I'd export it into a spreadsheet identify the errored rows then create another BO off the results from that

Eg BOErroredEmails with attributes back to the BO that has the errors.

PointsWell

Another alternative is to run a process using the INDEX_OF against every instance and then set a flag on the BO with either a count of the number of @ or just ExcessEmails= Y/N

Then run the query on ExcessEmails = Y or EmailCount>1