Update day to second interval column in selection from time gaps table by parsing strings containing time differences using REGEXP_SUBSTR

An apparently trivial challenge that still took me bit longer than expected. What is the situation: I have a table with two columns (of interest). One is a column called gap, with values like these:

Each value represents a time difference. These values are strings – not much use to calculate with. Therefore a second column in the table – called timegap – is defined, of type INTERVAL DAY TO SECOND. The challenge I am facing is to set the value of this column timegap based on the time difference described by the string in column gap.

With a little bit of SQL agility, using for example some regular expressions and an interesting update on [the result of] an inline view, it turns out to be doable in a fairly simple SQL statement.

The first step is to extract the HOUR, MINUTE and SECOND parts from this query. I do this using regular expressions and the SQL function REGEXP_SUBSTR to extract the value matched by the regular expression pattern. The query for this is:

select  rtrim(regexp_substr(gap, '[0-9]{1,2}h'),'h') h
,       rtrim(regexp_substr(gap, '[0-9]{2}'''),'''') m
,       rtrim(regexp_substr(gap, '[0-9]{2}"'),'"') s
,       id
from   standing

This returns the values for hours, minutes and seconds for the gap column in table Standing.

The results from this query can be used to create INTERVAL DAY TO SECOND instances:

with gaps as
(
 select  rtrim(regexp_substr(gap, '[0-9]{1,2}h'),'h') h
 ,       rtrim(regexp_substr(gap, '[0-9]{2}'''),'''') m
 ,       rtrim(regexp_substr(gap, '[0-9]{2}"'),'"') s
 ,       id
 from   standing
)
select numtodsinterval(nvl(h,0),'HOUR')
       + numtodsinterval(nvl(m,0),'MINUTE')
       + numtodsinterval(nvl(s,0),'SECOND') gap
,      id
from   gaps

Using these INTERVAL values – I can perform the final update of table STANDING. The update statement is created like this:

update ( select numtodsinterval(nvl(h,0),'HOUR')
                + numtodsinterval(nvl(m,0),'MINUTE')
                + numtodsinterval(nvl(s,0),'SECOND') gap
         ,      id
         ,      timelag
         from   ( select  rtrim(regexp_substr(gap, '[0-9]{1,2}h'),'h') h
                  ,       rtrim(regexp_substr(gap, '[0-9]{2}'''),'''') m
                  ,       rtrim(regexp_substr(gap, '[0-9]{2}"'),'"') s
                  ,       id
                  ,       timelag
                  from   standing
                )
       )
 set    timelag = gap

Note how the inline view retrieves the values from the table and is subsequently updated ‘in situ’.