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

Lucas Jellema 2
0 0
Read Time:2 Minute, 7 Second

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

About Post Author

Lucas Jellema

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director and Oracle Developer Champion. Solution architect and developer on diverse areas including SQL, JavaScript, Kubernetes & Docker, Machine Learning, Java, SOA and microservices, events in various shapes and forms and many other things. Author of the Oracle Press book Oracle SOA Suite 12c Handbook. Frequent presenter on user groups and community events and conferences such as JavaOne, Oracle Code, CodeOne, NLJUG JFall and Oracle OpenWorld.
Happy
Happy
0 %
Sad
Sad
0 %
Excited
Excited
0 %
Sleepy
Sleepy
0 %
Angry
Angry
0 %
Surprise
Surprise
0 %

Average Rating

5 Star
0%
4 Star
0%
3 Star
0%
2 Star
0%
1 Star
0%

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

  1. 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”+’, ‘::’ ) )

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

Comments are closed.

Next Post

Tour de France 2011 - Analysis using ADF DVT Graphs - Part 2 - Gap with Cadel for Top 5 throughout the stages

This article uses the statistics about this year’s Tour de France to visualize the gap between each of the top 5 riders and Cadel Evans – throughout the 21 stages. It produces a line chart that provides easy insight in the history of this year’s event – making it abundantly […]
%d bloggers like this: