regex - Oracle 11g get all matched occurrences by a regular expression -

i'm using oracle 11g , use regexp_substr match occurrences given pattern. example

 select   regexp_substr('txa233141b ta233141 ta233142 ta233147 ta233148',   '(^|\s)[a-za-z]{2}[0-9]{5,}(\s|$)') "regexp_substr"   dual; 

returns first match ta233141 return other occurrences match regex, meaning ta233142 ta233147 ta233148.

regexp_substr returns 1 value. turn string pseudo-table , query matches. there's xml-based way of doing escapes me @ moment, using connect-by works, long have 1 source string:

select regexp_substr(str, '[^ ]+', 1, level) substr (     select 'txa233141b ta233141 ta233142 ta233147 ta233148' str dual ) connect level <= length(regexp_replace(str, '[^ ]+')) + 1; 

... gives you:

substr              -------------------- txa233141b            ta233141              ta233142              ta233147             ta233148             

... , can filter simpler version of original pattern:

select substr (     select regexp_substr(str, '[^ ]+', 1, level) substr     (         select 'txa233141b ta233141 ta233142 ta233147 ta233148' str         dual     )     connect level <= length(regexp_replace(str, '[^ ]+')) + 1 ) regexp_like(substr, '^[a-za-z]{2}[0-9]{5,}$');  substr              -------------------- ta233141              ta233142              ta233147              ta233148              

which isn't pretty, neither holding multiple values in 1 field.