Use IBM i SQL to validate email addresses in Customer Master File 

 May 12, 2018

By  NickLitten

IBM i SQL to validate Email

So, we have a customer master file that’s been around for years. Users have been keying in customer info and sometimes adding email addresses, sometimes not, sometimes entering nonsense, or even just text that says things like “this chap has no email so don’t email him”.

Yes, it’s a filthy dirty file filled with grubby email addresses. #grubby

It’s time to get cleaning….

Cleanup the email addresses in any database

Luckily it is super easy to do a quick and basic email validity check using SQL:

SELECT * FROM 'file-name' WHERE 'email-field' NOT LIKE '%_@__%.__%'

This is a basic selection that will check for really obviously malformed email address. It does not check if an email address is actually *real* or not.

We can add some more checks to this without getting into regular expression madness:

select * from 'file-name'
 where 'email-field' not like '%_@__%.__%' -- Must be someone@something.somewhere
or email not like '%@%.%' -- Must contain at least one @ and one
or email like '%..%' -- Cannot have two periods in a row
or email like '%@%@%' -- Cannot have two @ anywhere
or email like '%.@%' or email like '%@.%' -- Cannot have @ and . next to each other
or email like '%.cm' or email like '%.co' -- Unlikely. Probably typos 
or email like '%.or' or email like '%.ne' -- Missing last letter

Tweak it to your heart’s delight but it’s a simple and easy way to validate a file that I’ve used dozens of times.Ā  šŸ™‚


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:

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

Get In Touch

Iā€™m always looking for awesome input, feedback and critique!


Snug CBD

 20% Discount

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