CODEWITHSUNDEEP
sql
user3785698
user3785698

Reputation: 1

SQL- using padding

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?

Upvotes: 0

Views: 44

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 81930

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

Upvotes: 1

RoMEoMusTDiE
RoMEoMusTDiE

Reputation: 4824

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)

Upvotes: 0

Related Questions