我想让MonthlyPayment具有列float PaymentAmount数值(18,3)

我在PaymentAmount中有这样的数据:

PaymentAmount
--------------
1230.25
829122.35

我想要这样的退还付款金额:
PaymentAmount
--------------
000000001230.25
000000829122.35

我使用了此查询,但它返回:
PaymentAmount
--------------
000000000001230
000000000829122

查询:
SELECT ISNULL(replace(str(MonthlyPayment.PaymentAmount,15,0),' ','0'),SPACE(15))
FROM MonthlyPayment

最佳答案

这将适用于任何版本的SQL Server

SELECT RIGHT('000000000000' + CAST(CAST(PaymentAmount AS NUMERIC(18,2)) AS VARCHAR(20)),15)
FROM MonthlyPayment

Eample:

DECLARE @PaymentAmount AS DECIMAL (18,3) = 1230.256;
SELECT RIGHT('000000000000' + CAST(CAST(@PaymentAmount AS NUMERIC(18,2)) AS VARCHAR(20)),15)

DECLARE @PaymentAmount1 AS DECIMAL (18,3) = 829122.35;
SELECT RIGHT('000000000000' + CAST(CAST(@PaymentAmount1 AS NUMERIC(18,2)) AS VARCHAR(20)),15)

关于sql - 如何替换零并放入十进制数字?,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/39272580/

10-11 01:22