在Oracle中转换为十进制时如何保留前导零

本文介绍了在Oracle中转换为十进制时如何保留前导零的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我在oracle中运行以下查询.

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 ....

在这里，我得到的结果(cesg_amt)为-156.57.但我需要结果为-0000000000156.57. 我需要保留-的前导零(前导0和小数点后两位).

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).

我在查询中尝试使用to_char(ta.CESG_amt, '0000000000000.00') AS cesg_amt，但没有用.

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

您能帮我在DECIMAL字段中进行以下操作以获得以下结果吗?

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

推荐答案

您可以使用这种格式:

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