Objective:
To find the validity of the email address using Oracle Pl/sql and to find the invalid email address from a object like table using Oracle Sql.
Using Pl/Sql:
DECLARE
b_isvalid BOOLEAN;
BEGIN
b_isvalid :=
REGEXP_LIKE (‘anyaddress@xyz123.com’,
‘^[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:
Using SQL:
WITH t AS
(SELECT ‘xxxxx.1981@gmail.com’ email FROM dual
UNION ALL
SELECT ‘yyyyy@gmail.com’ FROM dual
UNION ALL
SELECT ‘2222.1980@gmail.com’ FROM dual
UNION ALL
SELECT ‘rrrrrrr@parker.1989@gmail.com’ FROM dual
)
SELECT *
FROM t
a
WHERE not REGEXP_LIKE (EMAIL,’^[A-Za-z]+[A-Za-z0-9.]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}$’);
Output:
