Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1902
  • Last Modified:

sql right padding zeros after decimal

Hi, I am trying to add right padding zeros to my decimal values but I get stuck.

So far I can add left padding zeros and remove the dcimal point,
the issue is that when I get a value of 2.43 then I can convert it to 000243 but if its 2.4 then the converision is to 000024. I need to modify the query so I can have such result:
000243
000240
etc..

 
SELECT RIGHT('0000000000' + CONVERT(VARCHAR,REPLACE([CustFinTrxnNetAmt], '.', '')), 10)  FROM [Transactions]

Open in new window


Thank you for your answer.
0
rkanabus
Asked:
rkanabus
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
first, convert to varchar, then remove comma, finally left pad:
SELECT RIGHT('0000000000' + REPLACE( CONVERT(VARCHAR(10), CONVERT(DECIMAL(20,4), [CustFinTrxnNetAmt])) , '.', '') , 10)  FROM [Transactions]

Open in new window

0
 
rkanabusAuthor Commented:
Excellent, thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now