Funky IBM i Email Validation Program using SQL Regex

IBM i

Jun 13

Following on from my previous blog (basic email validation) I decided that it would much nicer to (a) have the regex stored in a data area so that we can easily play with it to try different validation scenarios and (b) lets have a shot at a much stricter validation because there is an official standard – RFC5322.┬áLike our basic validation, this doesn’t check that it’s a valid address by checking online, but it does perform some stricter checks for which characters are being used and in which order.

So, lets start with this regex:

General Email Regex (RFC 5322 Official Standard)

(?:[a-z0-9!#$%&’*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&’*+/=?^_`{|}~-]+)*|”(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21\x23-\x5b\x5d-\x7f]|\\[\x01-\x09\x0b\x0c\x0e-\x7f])*”)@(?:(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])?|\[(?:(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?|[a-z0-9-]*[a-z0-9]:(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21-\x5a\x53-\x7f]|\\[\x01-\x09\x0b\x0c\x0e-\x7f])+)\])

*phew* that’s a load of gobbledygook.

You will notice that this regex also has lots of single quotes, double quotes and other naughtiness in it?

As an IBM i Developer I’m sure you are already cringing trying to figure out how to get this variable into your code.

So let’s tidy that variable, plonk it in a data area and create it like this:

CRTDTAARA DTAARA(LITTENN/VLD8EMAIL) TYPE(*CHAR) LEN(1024) VALUE('(?:[a-z0
-9!#$%&''*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&''*+/=?^_`{|}~-]+)*|"(?:[\x01-\x
08\x0b\x0c\x0e-\x1f\x21\x23-\x5b\x5d-\x7f]|\\[\x01-\x09\x0b\x0c\x0e-\x7f]
)*")@(?:(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-
9])?|\[(?:(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(?:25[0-5]|2[0-4]
[0-9]|[01]?[0-9][0-9]?|[a-z0-9-]*[a-z0-9]:(?:[\x01-\x08\x0b\x0c\x0e-\x1f\
x21-\x5a\x53-\x7f]|\\[\x01-\x09\x0b\x0c\x0e-\x7f])+)\])') TEXT('validate 
email regular expression')

This gives us this beautiful thing:

Funky IBM i Email Validation Program using SQL Regex 1

We can then tinker with our previous code, a couple of quick little changes to the email_is_valid procedure, and simply read the regular expression in from a data area like this:

dcl-proc email_is_valid;
 dcl-pi *n IND;
   myEmail like(email);
 end-pi;
 dcl-s myCount uns(10);
 dcl-s myEmailValidationRegex varchar(1024);
 dcl-ds vld8email dtaara len(1024);

 in vld8email;
 myEmailValidationRegex = %trim(vld8email);

 exec sql
   set :myCount = regexp_count(:myEmail, :myEmailValidationRegex);

 If myCount = 1;
   return *on;
 else;
   return *off;
 endif;
end-proc;

A much nicer solution all round.

Plus the regex analyzer for this regex is way more mental:

Funky IBM i Email Validation Program using SQL Regex 2

#timeforacoffee

Follow

About the Author

IBM i Software Developer, Digital Dad, AS400 Anarchist, RPG Modernizer, Alpha Nerd and Passionate Eater of Cheese and Biscuits. Nick Litten Dot Com is a mixture of blog posts that can be sometimes serious, frequently playful and probably down-right pointless all in the space of a day. Enjoy your stay, feel free to comment and in the words of the most interesting man in the world: Stay thirsty my friend.

  • Eli Shats says:

    Hi Nick
    Great possibility to check but the data area contents are the complex ones
    Nick can you please give a hint what may be wrong email address variable ( I tried my workoffice address once inside of char(50) variable and another time as varchar(50) but got both of the case the same negative results “as if email address was not valid )

  • Eli Shats says:

    Sorry Nick I was wrong( placed some spaces inside of data area while copying) , Great example ! Have a nice weekend !

  • >