SQL Email Validation (Basic)

It’s quite common that you will store an email address in your database, but before you do so it might be worth making sure that it’s at least a valid email address.

Now, this is a very basic validation, as it will only make sure that the string is in the correct email “structure”, it’s not going to check to make sure the domain is valid or if the email address actually exists; that requires some rather heavy lifting.

When inserting your row, add this case statement for your email address column:

case 
   when regexp_like(CUSTOMER_EMAIL_ADDRESS, '^[A-Za-z0-9_-]+(\.[A-Za-z0-9_-]+)*@[A-Za-z0-9_-]+(\.[A-Za-z0-9_-]+)*\.([a-z]{2,4})$') 
   then EMAIL_ADDRESS
   else  null 
end as "EMAIL_ADDRESS"

You can swap out the else statement null to something that is more useful if you want, i.e. “Invalid Email” or whatever you need.

The regexp_like function works like this:

[capture-group-1}@[capture-group-2].[capture-group-3]

Capture Group 1:

The only accepted characters are alphanumeric between 0 – 9, Aa – Zz, except it will allow periods (.) and hyphens (-) as well. This capture group starts at the first character of the string and ends when it reaches the @ symbol.

Capture Group 2:

Does the exact same thing as Capture Group 1 except finishes when it reaches the lasts period (.).

Capture Group 3: 

Captures everything after the last period (.) and only allows alphabetical characters that are between 2 –  4 characters in length.

For endings such as .co.uk, that’s capture between Capture Group 2 and Capture Group 3.

If the email address doesn’t follow this convention, then it will instead insert null into the table.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s