select REGEXP_SUBSTR(str, '[[:alnum:].+]+@gmail.com', 1, level) as substr
from   (select ' John@yahoo.co.in , Jacob.foo@gmail.com,   Bob@rediff.com,Lisa@abc.com, sam@gmail.com , sam.bar+stackoverflow@gmail.com, rita@yahoo.com, foobar ' as str
        from   DUAL)
connect by level <= LENGTH(REGEXP_REPLACE(str, '[^,]+')) + 1;