Keep default value as 0.0 for 0 or NULL for numeric column I have a numeric column Number(28,3) in which I have to insert value as 0.0 ,
whenever I encounter 0 or NULL/blank at the time of data insertion.

I have tried below :

NVL(col_name,0.0)


but it's just replacing null values with 0
This is just a matter of displaying the data as required:

SQL&gt; create table t1(c1 number(28,3));

Table created.

SQL&gt; insert into t1 values(nvl(null, 0.0));

1 row created.

SQL&gt; select * from t1;

        C1
----------
         0

SQL&gt; col c1 format 999990.0
SQL&gt; select * from t1;

       C1
---------
      0.0