[FIXED] How to preserve leading zeros when converting to a decimal in oracle

Issue

I am running the below query in oracle.

WITH
ta AS (
     SELECT account_coid
        ,txn_id
        ,cbdev.cbzdt(effective_date) AS effective_date
        ,cbdev.cbchr(utl_raw.substr(txn_data, 113, 20)) AS CESG_amt
     FROM bs_transaction
     WHERE sub_type = 127469880)
SELECT
cast(ta.CESG_amt as DECIMAL (20,2)) AS cesg_amt
from ta 
inner join ....

Here, i m getting the result (cesg_amt) as -156.57. But i need the result as -0000000000156.57.
I need the leading zeros with - retained (leading 0's and also the two digits after the decimal).

I have tried as to_char(ta.CESG_amt, '0000000000000.00') AS cesg_amt in the query but of no use.

Can you please help me what needs to be done in the DECIMAL field to get the result as below.

Solution

You may use such a formatting :

select to_char(-156.57,'fm0000000000000D00','NLS_NUMERIC_CHARACTERS = ''.,''')
       as Result
  from dual;

RESULT
-----------------
-0000000000156.57

Answered By - Barbaros Özhan

Answer Checked By - Candace Johnson (PHPFixing Volunteer)