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

2

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:

Image

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’.

Share.

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director for Fusion Middleware. Consultant, trainer and instructor on diverse areas including Oracle Database (SQL & PLSQL), Service Oriented Architecture, BPM, ADF, Java in various shapes and forms and many other things. Author of the Oracle Press book: Oracle SOA Suite 11g Handbook. Frequent presenter on conferences such as JavaOne, Oracle OpenWorld, ODTUG Kaleidoscope, Devoxx and OBUG. Presenter for Oracle University Celebrity specials.

2 Comments

  1. Anton Scheffer on

    Is this the same as
    update standing
      set timelag = to_dsinterval(case when instr( gap, ‘h’ ) > 0 then ‘0 ‘ else ‘0 0:’ end || translate( gap, ”’h”+’, ‘::’ ) )

    • Anton,

      It certainly seems that way. It also seems that I have managed to turn something rather simple into something not so simple. One of my specialties I am afraid. I did have this nagging notion that it could be done in a more straightforward way. Well, thanks for pointing it out.

      Lucas

Leave a Reply