Any function in Oracle similar to COALESCE, but instead of NULL works on ERRORs-CodePudding

I have a situation where I need to convert a numeric column value into time. It's a 6 digit field, but unfortunately, different processes over the years inserted data in different format, some HHMM, and others HHMMSS. Let's call this column colTime. I use colTime in combination with another 8 digits numeric field which contains a date in YYYYMMDD format, let's call this colDate.

It is being used as below to construct a TIMESTAMP in UTC zone:

select TO_CHAR(SYS_EXTRACT_UTC(TO_TIMESTAMP(CONCAT(NULLIF(colDate,0), LPAD(NULLIF(colTime,0),4,0)), 'YYYY-MM-DD HH24:MI')), 'YYYY-MM-DD"T"HH24:MI:SS.FFTZR') from tab1;

The problem here obviously is that the colTime may contain 4 OR 6 digit data so I cannot know the correct LPAD number in advance. When the above statement encounters a 6 digit field it throws an error.

I was thinking if I have a function similar to COALESCE that can execute the second argument if the first one returns an error then I'd be able to accommodate LPAD 4 and 6 cases.

I can use a CASE statement, but was hoping for something more graceful.

CodePudding user response：

You can still use COALESCE if you also use the DEFAULT NULL ON CONVERSION ERROR syntax in TO_TIMESTAMP:

select
    coalesce
    (
        to_timestamp('2021-01-01' || ' ' ||the_time default null on conversion error, 'YYYY-MM-DD HHMI'),
        to_timestamp('2021-01-01' || ' ' ||the_time default null on conversion error, 'YYYY-MM-DD HHMISS')
    )
from
(
    select '0102'   the_time from dual union all
    select '010203' the_time from dual
);

CodePudding user response：

You can use regular expressions to find out which format the data comes from and then you can convert it appropriately.

For example:

with
d (col_time) as (
  select '0215' from dual
  union all select '183207' from dual
  union all select '12:34:56' from dual
  union all select 'really-bad-data' from dual
)
select d.*,
  case when regexp_like(col_time, '^[0-9]{4}$') then 'Short Time Format'
       when regexp_like(col_time, '^[0-9]{6}$') then 'Long Time Format'
       else 'Other Time Format'
  end as guessed_format
from d;

Result:

 COL_TIME         GUESSED_FORMAT    
 ---------------- ----------------- 
 0215             Short Time Format 
 183207           Long Time Format  
 12:34:56         Other Time Format 
 really-bad-data  Other Time Format