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

5
  • 1
    "It's not possible." david-gilbertson.medium.com/… Commented Jul 25, 2023 at 16:19
  • 1
    stackoverflow.com/questions/201323/… Commented Jul 25, 2023 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. Commented Jul 25, 2023 at 23:13
  • The version is 7.2 Commented Jul 26, 2023 at 7: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. Commented Jul 26, 2023 at 17:20

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.