Introduction
This post describes the steps required for email validation using PL/SQL Function
Script for Email Validation using Pl/Sql 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;
Queries
Do drop a note by writing us at doyen.ebiz@gmail.com or use the comment section below to ask your questions.
