EMAIL VALIDATION USING REGULAR EXPRESSIONS

This is what you need

select email from <table-name> 
where REGEXP_LIKE (EMAIL,'^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}$')

SQL> WITH t1 AS (SELECT 'test.test@gmail.com' email FROM DUAL
  2              UNION
  3              SELECT 'test2_test2@gmail.com' email FROM DUAL
  4              UNION
  5              SELECT 'test3@test3@gmail.com' email FROM DUAL)
  6  SELECT email
  7    FROM t1
  8   WHERE REGEXP_LIKE (EMAIL,
  9                      '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}$');


EMAIL
---------------------
test.test@gmail.com
test2_test2@gmail.com

SQL>

References:

https://community.oracle.com/thread/2430400

http://psoug.org/snippet/Regular-Expressions--REGEXP_LIKE_883.htm