SQL- using padding

Question

Need to sum(amounts) ,give it '0' padding of 10 character,replace the decimal with blank

eg:

Amount
34.56
45.12
12.23

Answer should look like 0000009191

+ cast((SELECT sum([amount]) from #clm2) as CHAR(10))

how can i do this?

Which database engine? Do you really need to do it in the database and not in the presentation layer? — Sami Kuhmonen, Apr 11 '17 at 03:39
Possible duplicate of [Pad a string with leading zeros so it's 3 characters long in SQL Server 2008](http://stackoverflow.com/questions/16760900/pad-a-string-with-leading-zeros-so-its-3-characters-long-in-sql-server-2008) — qxg, Apr 11 '17 at 04:02

score 1 · Answer 1 · answered Apr 11 '17 at 03:43

1

If 2012+, consider Format()

Declare @Yourtable table (amount decimal(10,2))
Insert Into @Yourtable values
(34.56),
(45.12),
(12.23)

Select Format(sum(Amount*100),'0000000000')
 From  @YourTable

Returns

0000009191

answered Apr 11 '17 at 03:43

John Cappelletti

79,615
7
44
66

What do you mean with "2012+"? There is no such SQL standard – Apr 11 '17 at 05:47

score 0 · Answer 2 · answered Apr 11 '17 at 03:41

you can try this

for padding

select '0000000000' + cast(fieldname as varchar)

and replace dots with blank space with padding

select replace('0000000000' + cast(fieldname as varchar),'.','')

get 10 characters from right

select right(replace('0000000000' + cast(fieldname as varchar),'.',''),10)

SQL- using padding

2 Answers2