assign zero before decimal value in oracle sql-CodePudding

I have table tst_fr where i have column Dur which is varchar datatype. In this table there are lot of values which is starting with decimal for example .16.

I need to assign 0 before that decimal value for example 0.16.

I tried with below query but it didnt worked and just returning as 0 for all the decimal starting values:

Select to_char(round(to_number(DUR) / (1024*1024), 3),0999) as DUR
from tst_fr

CodePudding user response：

You can use:

Select to_char(
         round(to_number(DUR)/(1024*1024), 3),
         'fm9990.999'
       ) as DUR
from   tst_fr

Which, for the sample data:

CREATE TABLE tst_fr (dur) AS
SELECT 0.16 * 1024*1024 FROM DUAL UNION ALL
SELECT 1234.5678 * 1024*1024 FROM DUAL;

Outputs:

DUR

0.16

1234.568

DUR
0.16
1234.568

db<>fiddle here