Problem with INDEX_OF

If you have questions or if you want to share your opinion about Aware IM post your message on this forum
Post Reply
rocketman
Posts: 1239
Joined: Fri Jan 02, 2009 11:22 pm
Location: Preston UK
Contact:

Problem with INDEX_OF

Post by 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 [email protected];[email protected]

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
Rocketman

V8.7 Developer Edition. Server 2016 Standard edition. MySql 5.5
BobK
Posts: 544
Joined: Thu Jan 31, 2008 2:14 pm
Location: Cincinnati, Ohio, USA

Re: Problem with INDEX_OF

Post by 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

Code: Select all

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:

Code: Select all

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.
Bob
PointsWell
Posts: 1457
Joined: Tue Jan 24, 2017 5:51 am
Location: 'Stralya

Re: Problem with INDEX_OF

Post by PointsWell »

rocketman wrote: Tue Feb 08, 2022 2:57 pm
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 [email protected];[email protected]
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
Posts: 1239
Joined: Fri Jan 02, 2009 11:22 pm
Location: Preston UK
Contact:

Re: Problem with INDEX_OF

Post by 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.
Rocketman

V8.7 Developer Edition. Server 2016 Standard edition. MySql 5.5
PointsWell
Posts: 1457
Joined: Tue Jan 24, 2017 5:51 am
Location: 'Stralya

Re: Problem with INDEX_OF

Post by 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
Posts: 1457
Joined: Tue Jan 24, 2017 5:51 am
Location: 'Stralya

Re: Problem with INDEX_OF

Post by 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
Post Reply