Search code examples
sqldatabasedb2number-formattingibm-midrange

SQL DB2 - add leading zero, align right

SQL DB2/400

Trying to add leading zero, align right.

 NUMERIC     REQUIRED_OUTPUT   
   .0000              0.0000             
  1.0000              1.0000            
   .0000              0.0000             
100.0000            100.0000

Part way there...

SELECT NBUN62 as NUMERIC ,char(NBUN62) as CHARACTER,                
       replace(char(NBUN62), '.' , '0.') AS ADD_LEADING_ZERO 
FROM Cost_File

 NUMERIC   CHARACTER    ADD_LEADING_ZERO    
   .0000   .0000        0.0000              
  1.0000   1.0000       10.0000             
   .0000   .0000        0.0000              
   .0000   .0000        0.0000              
100.0000   100.0000     100.0000
Solution

  • For the NUMERIC(9,4) data type you need the function VARCHAR_FORMAT() with the format '99990.9999':

    SELECT NBUN62, 
       VARCHAR_FORMAT(NBUN62, '99990.9999') AS ADD_LEADING_ZERO
FROM Cost_File;

    See the demo.