SQL & PL/SQL

Hi,

I am currently faced with a little issue where I have values for time spent (duration) in a varchar2(14) field which are stored in the format of either hhhh:mm:ss.ssss or hh:mm:ss.ssss i.e. 0010:40:34.1232 or 01:40:23.2345. I would like to return data using only SQL and the format should be hh:mm:ss meaning trimming any instances of leading 0's and all the milliseconds. Therefore, when the times are like 0009:56:45.3456 then I would like this to be returned as 09:56:45.

I have got up to a certain point and could do with some advice.

I am currently using the case statement and the substr function to derive this. So currently this is the logic I am using:

CASE
WHEN SUBSTR(duration_column, 1,4) = '0000'
THEN SUBSTR(duration_column, 1,10)
ELSE substr(duration_column,1,8)
END

What I now would like to do is when there are instances where the times have hours with four digits is to trim the first 2 leading zeros to ensure we get times in the format of hh:mm:ss. The 2 leading zeros are not required but the system occasinally stores values in the database like this because database field it is stored in is a varchar2 field.

Any help, advice or guideance will be greatly appreciated.

Thanks