How to Validate Email Address Using PL/SQL?

How to Validate Email Address Using PL/SQL?

Written by
Vinish Kapoor is a seasoned software development professional and a fervent enthusiast of artificial intelligence (AI). His impressive career spans over 20 years, marked by a relentless pursuit of innovation and excellence in the field of information technology. As an Oracle ACE, Vinish has distinguished himself as a leading expert in Oracle technologies, a title awarded to individuals who have demonstrated their deep commitment, leadership, and expertise in the Oracle community.

Here I am giving an example to validate email address using PL/SQL regular expressions (REGEXP) in Oracle.

Validate Email Using PL/SQL Program

In the following PL/SQL program, it will validate the email address using the REGEXP_LIKE function.

SET SERVEROUTPUT ON;

DECLARE
   b_isvalid   BOOLEAN;
BEGIN
   b_isvalid :=
      REGEXP_LIKE ('[email protected]',
                   '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}$');

   IF b_isvalid
   THEN
      DBMS_OUTPUT.put_line ('It is a valid email address.');
   ELSE
      DBMS_OUTPUT.put_line ('It is Not a valid email address.');
   END IF;
END;
/

Output

It is a valid email address.
PL/SQL procedure successfully completed.

Get the List of Invalid Email Addresses from a Table Using SQL

The following SQL query will retrieve all the invalid email addresses stored in the EMAIL column of EMPLOYEES table in Oracle.

SELECT *
  FROM EMPLOYEES
 WHERE NOT REGEXP_LIKE (email,
                        '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}$');

Similarly, to get the list of valid email addresses, delete NOT from the above query.

See also:

This Post Has 6 Comments

  1. Jonas Wegelius August 3, 2020

    this does not pass some valid email addresses, example:
    [email protected]

    1. Vinish Kapoor August 3, 2020

      Hi Jonas,

      I tested for the email id you mentioned and it is returning the correct result:

      Select
  'Valid'
From
  dual
Where
  Regexp_Like ( '[email protected]',
         '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}$' );

      The above query returning a row, meaning it is the valid address.

  2. anuj November 25, 2020

    can email id start with a dot (.) , this is passing it.

    1. Lokesh May 16, 2021

      no ishould check only @ if it is not present in email it should show bounced in another column, and i want full code can you please do it in stored procedure

  3. Lokesh May 16, 2021

    Hi I am Lokesh, I have one scenario can solve it and share that in plsql procedure and share to me pplease and expalin things how u solved

  4. Chris December 3, 2021

    Can you post an example of using REGEXP_LIKE to validate a URL string?

Comments are closed.