1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
| with cte_prep (id, col, deb, fin) as
(
select id, col
, '0' || regexp_substr(col, '[[:digit:]]{1,2}h[[:digit:]]{0,2}', 1, 1) || '00' end
, '0' || regexp_substr(col, '[[:digit:]]{1,2}h[[:digit:]]{0,2}', 1, 2) || '00' end
from (values (1, '5/7 7h30-20h')
, (2, '5/7 7h-20h' )
, (3, '24/7' )
, (4, '7/7 10h-18h' )
, (5, '3/7 4h-8h45' )) t (id, col)
)
select id, col
, case col when '24/7' then '7' else substr(col, 1, 1) end as jour
, case col when '24/7' then '00:00' else substr(deb, instr(deb, 'h')-2, 2) || ':' || substr(deb, instr(deb, 'h')+1, 2) end as deb
, case col when '24/7' then '23:59' else substr(fin, instr(fin, 'h')-2, 2) || ':' || substr(fin, instr(fin, 'h')+1, 2) end as fin
from cte_prep;
ID COL JOUR DEB FIN
-- ------------ ---- ----- -----
1 5/7 7h30-20h 5 07:30 20:00
2 5/7 7h-20h 5 07:00 20:00
3 24/7 7 00:00 23:59
4 7/7 10h-18h 7 10:00 18:00
5 3/7 4h-8h45 3 04:00 08:45 |
Partager