CODEWITHSUNDEEP
sqlsql-server
Bishoy Ezzat
Bishoy Ezzat

Reputation: 99

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

Upvotes: 2

Views: 546

Answers (3)

Sagar Shelke
Sagar Shelke

Reputation: 517

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)

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521093

If your version of SQL Server has REPLICATE available, then you can carefully pad the PaymentAmount column with the required number of zeroes to give the total string a width of 15 characters.

SELECT REPLICATE('0',
                 15 - LEN(CONVERT(VARCHAR, CONVERT(DECIMAL(14, 2), PaymentAmount))))
     + CONVERT(VARCHAR, CONVERT(DECIMAL(14, 2), PaymentAmount))
FROM MonthlyPayment

Upvotes: 2

Arulkumar
Arulkumar

Reputation: 13237

For SQL Server 2012 + you can use FORMAT

SELECT FORMAT(PaymentAmount, '00000000000#.##')
FROM MonthlyPayment

Sample execution:

DECLARE @PaymentAmount AS DECIMAL (18,2) = 1230.25;
SELECT FORMAT(@PaymentAmount,'00000000000#.##');
-- OUTPUT: 000000001230.25

DECLARE @PaymentAmount1 AS DECIMAL (18,2) = 829122.35;
SELECT FORMAT(@PaymentAmount1,'00000000000#.##');
-- OUTPUT: 000000829122.35

Upvotes: 3

Related Questions