Wednesday, 4 July 2018

Email Validation using PL/SQL Function


Email Validation Function using plsql function

CREATE FUNCTION f_email_validate (pi_email_id IN OUT VARCHAR2)
   RETURN BOOLEAN
IS
   lv_n_check_len      NUMBER;
   lv_b_check_in_at    BOOLEAN;
   lv_b_check_in_dot   BOOLEAN;
   lv_v_extn           VARCHAR2 (10);
BEGIN
   lv_n_check_len := LENGTH (pi_email_id);
   pi_email_id := LOWER (pi_email_id);

   SELECT REPLACE (pi_email_id, ' ', '')
     INTO pi_email_id
     FROM DUAL;

   SELECT REPLACE (pi_email_id, '"', '')
     INTO pi_email_id
     FROM DUAL;

   SELECT REPLACE (pi_email_id, '`', '')
     INTO pi_email_id
     FROM DUAL;

   SELECT REPLACE (pi_email_id, CHR (39), '')
     INTO pi_email_id
     FROM DUAL;

   SELECT REPLACE (pi_email_id, CHR (92), '')
     INTO pi_email_id
     FROM DUAL;

   lv_v_extn := SUBSTR (pi_email_id, INSTR (pi_email_id, '.', -1) + 1);

   IF lv_v_extn NOT IN
         ('com', 'org', 'net', 'edu', 'gov', 'us', 'biz', 'info', 'tv', 'cc',
          'ws', 'ac', 'as', 'be', 'ca', 'cc', 'de', 'dk', 'fm', 'gs', 'il',
          'jp', 'kz', 'lt', 'sk', 'in', 'ms', 'nz', 'ph', 'ro', 'sh', 'st',
          'tc', 'to', 'tv', 'uk', 'us', 'vg', 'vu', 'ws', 'za')
   THEN
      RETURN (‘Invalid’);
   END IF;

   FOR i IN 1 .. lv_n_check_len
   LOOP
      IF SUBSTR (pi_email_id, i, 1) = '@'
      THEN
         lv_b_check_in_at := TRUE;
      ELSIF SUBSTR (pi_email_id, i, 1) = '.'
      THEN
         lv_b_check_in_dot := TRUE;
      END IF;
   END LOOP;

   IF lv_b_check_in_at AND lv_b_check_in_dot
   THEN
      RETURN (Valid’);
   ELSE
      RETURN (Invalid’);
   END IF;
END;

No comments:

Post a Comment