在Oracle中转换为十进制时如何保留前导零 [英] How to preserve leading zeros when converting to a decimal in 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

这篇关于在Oracle中转换为十进制时如何保留前导零的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！