Bishoy Ezzat Bishoy Ezzat - 3 months ago 13
SQL Question

How to replace zeros and put a decimal number?

I want MonthlyPayment have column float

PaymentAmount
numeric(18,3)

I have data like this in PaymentAmount:

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


I want to make the returning Payment amount like this:

PaymentAmount
--------------
000000001230.25
000000829122.35


I used this query but It returns:

PaymentAmount
--------------
000000000001230
000000000829122


the query :

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

Answer

This will work with any version of 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)