Oracle Regular Expressions for Parsing XML
Sample SCHED_PARAM values:
1. Schedule Type='Weekly' StartDate='2013-03-11' FirstRunTime='08:00' RunEveryXWeeks='1' DaysOfWeek='32'
2. Schedule Type='MonthlyDayOfWeek' StartDate='2013-03-11' EndDate='2013-03-19' FirstRunTime='08:00' MonthlyNthOccurrence='0' MonthlyDayOfWeek='0' Months='4095'
R
Using the Regular Expression:
Expression 1:
select REGEXP_SUBSTR(REGEXP_SUBSTR(SCHED_PARAM, 'Type=''[[:alnum:]]*''', 1, 1, 'i'), '[^'']+', 1, 2) as Type from SYNCH_SCHED_DETAIL;
Returns:
1. Weekly
2. MonthlyDayOfWeek
Expression 2:
select REGEXP_SUBSTR(REGEXP_SUBSTR(SCHED_PARAM, 'StartDate=''([[:alnum:]]*\-*)*''', 1, 1, 'i'), '[^'']+', 1, 2) from SYNCH_SCHED_DETAIL;
Returns:
1. 2013-03-11
2. 2013-03-11
Expression 3:
select REGEXP_SUBSTR(REGEXP_SUBSTR(SCHED_PARAM, 'FirstRunTime=''([[:alnum:]]*\:*)*''', 1, 1, 'i'), '[^'']+', 1, 2) from SYNCH_SCHED_DETAIL;
Returns:
1. 08:00
2. 08:00
Expression 4:
select To_date(REGEXP_SUBSTR(REGEXP_SUBSTR(SCHED_PARAM, 'StartDate=''([[:alnum:]]*\-*)*''', 1, 1, 'i'), '[^'']+', 1, 2) || ' ' || REGEXP_SUBSTR(REGEXP_SUBSTR(SCHED_PARAM, 'FirstRunTime=''([[:alnum:]]*\:*)*''', 1, 1, 'i'), '[^'']+', 1, 2), 'YYYY-MM-DD HH24:MI') from SYNCH_SCHED_DETAIL;
Will return an oracle date value for the Start Date + First Run Time
I am sure there are better way to code the Reg Ex.
Please leave me comment below if you have a better way
