Split string by space and space as delimiter in Oracle with regexp

I had one requirement like one string will be there like "FATALITY, BODY SYS, TEAM BUILDING EVENT.THE EMPLOYEE WAS PARTICIPATING IN A TEAM BUILDING EVENT WHEN HE SUFFERED A HEART ATTACK. THE EVENT WAS FATAL." and I have another LOOK up table to match the certain word in the above string .If any word matches from this this sentence then the matching word should only need to return other wise null valves need to be there. This can be done with regexp_substr but not able to match the pattern.

input string :FATALITY, BODY SYS, TEAM BUILDING EVENT.THE EMPLOYEE WAS PARTICIPATING IN A TEAM BUILDING EVENT WHEN HE SUFFERED A HEART ATTACK. THE EVENT WAS FATAL.

Expected o/p: HEART,FATAL --this heart and fatal will be in lookup table.

Thanks In Advance.

CodePudding user response：

How about a simple INSTR function?

SQL> with string (id, col) as
  2    (select 1, 'FATALITY, BODY SYS, TEAM BUILDING EVENT.THE EMPLOYEE WAS PARTICIPATING IN A TEAM BUILDING EVENT WHEN HE SUFFERED A HEART ATTACK. THE EVENT WAS FATAL.' from dual union all
  3     select 2, 'THERE IS ONLY HEART IN HERE' from dual),
  4  lookup (word) as
  5    (select 'HEART' from dual union all
  6     select 'FATAL' from dual
  7    )
  8  select s.id,
  9    listagg(l.word, ', ') within group (order by l.word) result
 10  from string s join lookup l on instr(s.col, l.word) > 0
 11  group by s.id;

        ID RESULT
---------- ----------------------------------------
         1 FATAL, HEART
         2 HEART

SQL>