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.