SQL Server Pad With Leading Zeroes

Question

I can't seem to get the following result to pad with leading zeroes in SQL Server 2008. Please note, AMT14 is of type varchar, so this will take the AMT14 and cast is as a decimal, so it can execute the SUM function.

<<select right('0000000000000' + sum(cast(AMT14 as decimal(13,2))),13) from {table1}>>

Result: 656311.22
Desired Result: 00000656311.22

Possible duplicate of [Pad 0's to returned query and insert into temp table](https://stackoverflow.com/questions/20619696/pad-0s-to-returned-query-and-insert-into-temp-table) — Thom A, Sep 05 '18 at 16:07

score 0 · Answer 1 · answered Sep 05 '18 at 16:10

0

The problem is the value is cast to decimal and then you're adding zero to it. You just need to cast the result of the sum to a varchar and you should be good

declare @amt14 varchar(10) = 656311.22

select right('0000000000000' + cast(sum(cast(@AMT14 as decimal(13,2))) as varchar),13)

answered Sep 05 '18 at 16:10

paulbarbin

382
2
9

1

So the key here is convert the number to a string. The reason RTRIM works is because the numeric parameter is converted to string before calling the function. Any number of other string functions may have worked as well: LTRIM, UPPER, LOWER. – David Dubois Sep 05 '18 at 18:40
Thank you @David Dubois. That's very useful information. I was annoyed/confused that the database field which houses numeric values was of type varchar, requiring conversions for aggregates; however, I now understand why they did that. – Tennis Sep 07 '18 at 15:17

score 0 · Accepted Answer · answered Sep 05 '18 at 16:13

0

You can use RTRIM, hope it will help

select right('0000000000000' + RTRIM(sum(cast('656311.22' as decimal(13,2)))),13)

answered Sep 05 '18 at 16:13

Pawel Czapski

1,856
2
16
26

worked like a charm - RTRIM is exactly what I needed. Thank you! – Tennis Sep 05 '18 at 17:21
1

@Tennis, I am happy it helped. – Pawel Czapski Sep 06 '18 at 08:05

SQL Server Pad With Leading Zeroes

2 Answers2