You can use the below query
Select CASE When INSTR (testres,'|')>0 THEN SUBSTR (testres,1,INSTR (testres,'|')-1) ELSE testres END EventID1,
CASE When INSTR (testres,'|')>0 THEN SUBSTR (testres,INSTR (testres,'|')+1,LENGTH(testres))ELSE NULL END as EventId2,
testres,test from (
select
CASE When INSTR (test,'New Action')>0 THEN REPLACE(REPLACE(test,'New Action Item for Event ',''),' has been Added','')
When INSTR (test,'Updated Action')>0 THEN REPLACE(REPLACE(test,'Updated Action Item for Event ',''),' for Associated Event ','|')
When INSTR (test,' is deleted')>0 THEN REPLACE(REPLACE(test,'Action Item for Event ',''),' is deleted','')
END testres,
test from(
select 'Updated Action Item for Event 249 for Associated Event 9400' test from dual union
select 'New Action Item for Event 249 has been Added' from dual union
select 'Action Item for Event 249 is deleted' from dual
)A )B