I have data like this:

I want the SQL output to be

I tried with below query but I can't get proper mins.

substr(to_CHAR(ROWA), 1, length(ROWA) - 4) || 'Hrs' || 
substr(to_CHAR(ROWA), 2, length(ROWA) - 3) || 'Mins'

Format is like HHMMSS

CodePudding user response：

You can use simple string functions:

SELECT CASE
       WHEN LENGTH(rowa) > 4
       THEN SUBSTR(rowa, 1, LENGTH(rowa) - 4) || 'hr'
       END
    || CASE
       WHEN LENGTH(rowa) >= 4
       THEN SUBSTR(rowa, -4, 2) || 'min'
       WHEN LENGTH(rowa) > 2
       THEN SUBSTR(rowa, 1, LENGTH(rowa) - 2) || 'min'
       END AS duration
FROM   my_tests;     

Which, for the sample data:

CREATE TABLE my_tests ( RowA ) AS
SELECT '21788' FROM DUAL UNION ALL
SELECT '2278' FROM DUAL UNION ALL
SELECT '278' FROM DUAL UNION ALL
SELECT '241740' FROM DUAL;

Outputs:

fiddle