.st0{fill:#FFFFFF;}

Funky IBM i Email Validation Program using SQL Regex 

 June 13, 2018

By  NickLitten

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

NickLitten


IBM i Software Developer, Digital Dad, AS400 Anarchist, RPG Modernizer, Shameless Trekkie, Belligerent Nerd, Englishman Abroad 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 remember: If at first you don't succeed then skydiving probably isn't a hobby you should look into.

Nick Litten

related posts:

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

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
    __CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"cff50":{"name":"Main Accent","parent":-1},"a344d":{"name":"Accent Transparent","parent":"cff50"}},"gradients":[]},"palettes":[{"name":"Default","value":{"colors":{"cff50":{"val":"var(--tcb-skin-color-0)"},"a344d":{"val":"rgba(46, 138, 229, 0.85)","hsl_parent_dependency":{"h":210,"l":0.54,"s":0.78}}},"gradients":[]},"original":{"colors":{"cff50":{"val":"rgb(0, 178, 255)","hsl":{"h":198,"s":1,"l":0.5}},"a344d":{"val":"rgba(0, 178, 255, 0.85)","hsl_parent_dependency":{"h":198,"s":1,"l":0.5}}},"gradients":[]}}]}__CONFIG_colors_palette__

    Get In Touch

    I’m always looking for awesome input, feedback and critique!

    >