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
– Sami Kuhmonen, Commented Apr 11, 2017 at 3:39
Possible duplicate of Pad a string with leading zeros so it's 3 characters long in SQL Server 2008 — qxg
– qxg, Commented Apr 11, 2017 at 4:02

John Cappelletti · Accepted Answer · 2017-04-11 03:43:35Z

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, 2017 at 3:43

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

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

1 Comment

user330315 Over a year ago

What do you mean with "2012+"? There is no such SQL standard

RoMEoMusTDiE · Accepted Answer · 2017-04-11 03:41:05Z

0

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)

answered Apr 11, 2017 at 3:41

RoMEoMusTDiE

4,8341 gold badge19 silver badges26 bronze badges

Collectives™ on Stack Overflow

SQL- using padding

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related