Adding extra Zero's dynamically to Integer in fixed lenght format

Question

I need to display a sql column Amount in a format of 15 Integers and 3 decimal places (18 length).

Add extra zero's dynamically to complete the length 18
Decimal(.) will also be replaced by a Zero

For Example :

$2,494.60 will be displayed as 000000002494060

Is it possible to achieve it using SQL ?

Anthony Faull · Accepted Answer · 2017-06-05 08:48:34Z

1

You can use STR to fix the leading and trailing decimal places, and REPLACE to change spaces and dots into zeroes.

Query:

SELECT Amount
    , PaddedAmount = REPLACE(REPLACE(STR(Amount, 15, 2), '.', '0'), ' ', '0')
FROM
    (VALUES (12345.678), (1231312), (0.12345), (0), (123456789012.45)) AS V (Amount)

Output:

Amount               PaddedAmount
-------------------  ----------------
12345.67800          000000012345068
1231312.00000        000001231312000
0.12345              000000000000012
0.00000              000000000000000
123456789012.45000   123456789012045

answered Jun 5, 2017 at 8:48

Anthony Faull

18.1k5 gold badges58 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jayvee · Accepted Answer · 2017-06-05 08:48:11Z

0

declare @amount numeric(15,3) = 2949.60
select Right('000000000000000' + replace(cast(@amount as varchar(15)),'.','0'),15)

edited Jun 5, 2017 at 8:48

answered Jun 5, 2017 at 8:31

Jayvee

10.9k3 gold badges35 silver badges43 bronze badges

3 Comments

Abdul Over a year ago

Can you please recheck end of second line. I think it has some issue

Jayvee Over a year ago

the bracket was facing the other way :)

Abdul Over a year ago

Thanks alot @Jayvee

Collectives™ on Stack Overflow

Adding extra Zero's dynamically to Integer in fixed lenght format

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related