0

I need to do a function in an older version of informix that I need to validate a mail:

so I need a function that check the email pattern [a-zA-z0-9]@[a-zA-Z0-9].[a-zA-Z0-9] Also in the last one that also allow to put like (.net.uk) but that is not finished by '.' and not be '..' Also that don't use special characters like ',' or 'Ü'

The main problem is that I need to do it in a older version of informix so a lot of elements like REGEXP are not compatible

How can I solve this problem?

I try this:

CREATE FUNCTION is_valid_email(email_param VARCHAR(255))
  RETURNING INTEGER;

  DEFINE is_valid INTEGER;

  LET is_valid = 0;

  IF LENGTH(email_param) > 7 
    AND email_param LIKE '%_@_%._%' 
    AND email_param NOT LIKE '%__@__%.__%' 
    AND email_param NOT LIKE '%___@___%.___%'
    AND email_param NOT LIKE '%@%_'
    AND email_param NOT LIKE '%.@%'
    AND email_param NOT LIKE '%..%'
  THEN
    LET is_valid = 1;
  END IF;

  RETURN is_valid;
END FUNCTION;

BUT Is not working

Stefan Wuebbe
  • 2,109
  • 5
  • 17
  • 28
Martí Castell
  • 1
  • 1
    "It's not possible." https://david-gilbertson.medium.com/the-100-correct-way-to-validate-email-addresses-7c4818f24643 – HardCode Jul 25 '23 at 16:19
  • 1
    https://stackoverflow.com/questions/201323/how-can-i-validate-an-email-address-using-a-regular-expression – HardCode Jul 25 '23 at 16:24
  • You say you're using an "older version of Informix" — how much older? 10.x? 7.x? 5.x? Older? As pointed out by the articles already linked to, validating email addresses is hard work and the only reliable way to do it is send a test email to the address. The pattern-matching facilities in standard SQL are pretty pathetic. – Jonathan Leffler Jul 25 '23 at 23:13
  • The version is 7.2 – Martí Castell Jul 26 '23 at 07:13
  • IDS Version 7.2 is archaic — I'm not sure it was Y2K-compliant even, though clearly it works adequately. However, apart perhaps from switching to MATCHES instead of LIKE, there isn't much you can do with that. You could consider whether using the SYSTEM statement to run some more powerful analysis tool is a good idea. It is possible, but not a good idea. – Jonathan Leffler Jul 26 '23 at 17:20

0 Answers0