June 13

2 comments

Funky IBM i Email Validation Program using SQL Regex

By NickLitten

June 13, 2018

#SQL, #dtaara, #email, #IBM, #ibmi, #regex

Using SQLRPGLE to Validate email addresses in IBM i

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

  • 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 )

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

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

    Join the IBM i Community for FREE Presentations, Lessons, Hints and Tips

    >

    Snug CBD

     20% Discount

    I have partnered with SNUG CBD givING you Organic CBD
    20% discount code "NL20"