Code to validate email address in Oracle

 Compile it in the database and pass an email address to see if the email address is VALID, INVALID or UNKNOWN.

For example, if you pass following email addresses, the returning result will be as follow:

   aopu.mohsin@gmail.com      -  VALID

   aopu.mohsin@gmailcom       -  INVALID

   @gmail.com                 -  INVALID

   aopu@.com                  -  INVALID

   aopu.mohsin@gmail.         -  INVALID

   aopu. mohsin@gmail.com     -  INVALID

   NULL                       -  UNKNOWN

CREATE OR REPLACE FUNCTION xxcc_validate_email(p_email  IN  VARCHAR2)
   RETURN VARCHAR2

 IS
   v_at_pos         PLS_INTEGER;                -- position variable for at (@) sign
   v_dot_pos        PLS_INTEGER;                -- position variable for dot (.) sign
   v_length         PLS_INTEGER;                -- holds the length of email

   v_email_ch       VARCHAR2(1);                -- check character in an email
   v_result         VARCHAR2(10)  DEFAULT  'VALID';   -- result variable

BEGIN
   ----------------------------------------------------------
   -- variable assignments
   ----------------------------------------------------------
   v_at_pos     :=  INSTR(p_email, '@', -1);  -- position for last occurance of '@' sign
   v_dot_pos    :=  INSTR(p_email, '.', -1);  -- position for last occurance of '.' sign
   v_length     :=  LENGTH(p_email);          -- length of entire email address

   ----------------------------------------------------------
   -- First Round Check:
   --   Rules for invalid email address
   ----------------------------------------------------------
   IF p_email IS NULL            OR   -- email cannot be null
      v_length  < 5              OR   -- email length should be at least 5 characters
      v_at_pos  = 0              OR   -- email requires at least one '@' sign
      v_dot_pos = 0              OR   -- email requires at least one '.' sign
      v_at_pos  = 1              OR   -- email cannot start with '@' sign
      v_dot_pos = v_at_pos - 1   OR   -- dot (.) sign cannot be right before at (@) sign
      v_dot_pos = v_at_pos + 1   OR   -- dot (.) sign cannot be right after at (@) sign
      v_dot_pos = v_length       OR   -- email cannot end with dot '.' sign
      v_at_pos  = v_length       OR   -- email cannot end with at '@' sign
    
      /* double dots are not permitted */
      INSTR(SUBSTR(p_email, 1, (v_at_pos - 1)), '..') > 0 OR
      INSTR(SUBSTR(p_email, v_at_pos + 1), '..')      > 0 OR
    
      /* requires one '.' sign after '@' sign */
      INSTR(SUBSTR(p_email, v_at_pos), '.') = 0           OR
      (INSTR(p_email, '@') > 0 AND INSTR(p_email, '@', INSTR(p_email, '@') + 1) > 0) OR
    
      /* this following code finds out if the domain part is a number or a string */
      UPPER(SUBSTR(p_email, v_at_pos, v_length)) = LOWER(SUBSTR(p_email, v_at_pos, v_length))
   THEN
      v_result := 'INVALID';
   END IF;

   ----------------------------------------------------------
   -- Second Round Check:
   --   Invalid characters should not be in the email address.
   --   Validate using INSTR method
   ----------------------------------------------------------
   FOR i IN 1..v_length
   LOOP
      v_email_ch  :=  SUBSTR(p_email, i, 1);
    
      IF INSTR(' `~!#$%^&*"|(){}[]:;,<>?\/''''', v_email_ch) > 0
      THEN
         v_result := 'INVALID';
         EXIT;
      END IF;
    
   END LOOP;
 
   RETURN (v_result);

EXCEPTION
   WHEN OTHERS THEN
      RETURN 'UNKNOWN';
 
END xxcc_validate_email;

/