0 
0 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’.
Related posts:
OOW 2012: The Very Very Latest in Database Development (CON4792) 
The Very Very Latest in Database Development – slides from the Expertezed presentation Juggling with SQL Types in SQL and PL/SQL – fading borders between Collection and Table Analyzing Match Scores in ‘fuzzy searches’ – explaining the shades of gray in a non-binary world Oracle RDBMS 11gR2 – LISTAGG – New aggregation operator for creating (comma) delimited strings
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