WITH S_TRINGS AS(
    SELECT '85.88-02ggg@a_lia-s.spb.ru' AS STR FROM DUAL
    UNION ALL
    SELECT 'spb.stu--.ru.123@g-00-gle.com' FROM DUAL
    UNION ALL
    SELECT 'vasy.kiev.ru.9999.@asd.com' FROM DUAL
    UNION ALL
    SELECT '1f.rk.2@asd.com' FROM DUAL
    UNION ALL
    SELECT '1%#23@asd.com.ua' FROM DUAL
    UNION ALL
    SELECT 'a.a@bbb.ccc' FROM DUAL
    UNION ALL
    SELECT 'ser.gevo@gmail.com' FROM DUAL
    UNION ALL
    SELECT '1@ru' FROM DUAL),

S_TRINGS1 AS(
    SELECT * FROM S_TRINGS
    WHERE REGEXP_LIKE(STR,'^[^@]+@[^@]+\.*[^@]+$')--имя собака домен
    AND REGEXP_LIKE(STR,'^.*@[^.]([^.]+[.]?[^.]+)*[^.]$')--субдомены
    AND REGEXP_LIKE(STR,'^(([^.]+[.]?[^.]+) | [^.]+)+@.*$')--точки в имени
    AND REGEXP_LIKE(STR,'^(([^-]+-?[^-]+)|[^-]+)+@([^-]+-?[^-])+$')--дефисы в домене и имени
    AND REGEXP_LIKE(STR,'^[[:alnum:].-]+@[[:alnum:].-]+$')--левые знаки типа %#
)

SELECT * FROM S_TRINGS1;