WITH t AS 
  (SELECT 'this@_is_invalid_case' COL FROM dual
   UNION
   SELECT 'this is valid case' COL FROM dual
  )
   SELECT COL,
           CASE
             WHEN SUBSTR(COL,  1, 1) IN ('.', '@') THEN 'no'
             WHEN SUBSTR(COL, -1, 1) IN ('.', '@') THEN 'no'
             ELSE 'yes'
           END case_substr,
           CASE
             WHEN INSTR(COL, '@') > 0 THEN 'no'
             WHEN INSTR(COL, '.') > 0 THEN 'no'
             ELSE 'yes'
           END case_instr
   FROM t;

COL                   CASE_SUBST CASE_INSTR
--------------------- ---------- ----------
this is valid case    yes        yes
this@_is_invalid_case yes        no