Click here to Skip to main content
15,069,297 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more: , +
I have a table in Oracle 'tEvents', Containing column 'Action' which having string containg Two EventIDs as you can see in example. My requirement, is I need to Extrcat the Both EventIDs and update the colummn EventID1 and EventID2 respectively.The Table contains millions of record.
KEY | USERID | Action | EventID1 | EventID2 | Date

309629 | U099439 | Updated Action Item for Event 249 for Associated Event 9400 | NULL | NULL | 11-AUG-99
Posted
Updated 20-May-14 23:07pm
v2
Comments
Basmeh Awad 29-Jan-14 9:32am
   
the string in your action column always will be the same with diffrent EVENTID??????
Mohd Shamim Ansari 29-Jan-14 9:44am
   
String will be in different format with EventdID but in Some row it is not mandatory to be EventID and also some row contains 1 or 2 EventID.


For Example
Updated Action Item for Event 249 for Associated Event 9400
New Action Item for Event 249 has been Added
Action Item for Event 249 is deleted

Thanks for your Reply!
Peter Leow 29-Jan-14 9:55am
   
Please do not re-post.

1 solution

You can use the below query


SQL
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
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900