Remove decimals and add leading zeros

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Remove decimals and add leading zeros

Author

Topic

accessdbguru
Starting Member

26 Posts

Posted - 2012-08-17 : 11:36:54

I have this simple SQL statement and I need to make it specific character length, add leading zeros and remove the decimals.

SQL is below:

SELECT COUNT ([check number]) AS TotalChecks,
SUM ([Check Amount]) AS TotalAmount FROM SAccounts
INNER JOIN Checks ON SAccounts.ID = Checks.[Account ID] where Name='PrimerBank' and Checks.[Check Date]='2012-08-13'

Result is:
TotalChecks TotalAmount
56 456567.84

Should display as:
TotalChecks TotalAmount
000000056 000045656784

Summary:
TotalCheck - should have 9 spaces with leading zeros
TotalAmount - should have 12 spaces with leading zeros and remove the decimal point

Please Help! Thank you.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-17 : 11:43:02

Many of the experts on this forum would recommend against doing this type of formatting in SQL - instead, they would advise you to do this on the front-end or client application if you have one. But if you do want to do this in SQL, you can do it like this:

DECLARE @specificCharacterLength INT = 20;

SELECT
	RIGHT(REPLICATE('0',@specificCharacterLength) + REPLACE(CAST(TotalChecks AS VARCHAR(32)),'.',''),@specificCharacterLength)

Edit: A word of caution though. If your data types are not exact numeric types (for example if they are float), the results may not be what you expect. If that is the case, cast the float to a decimal or another exact numeric instead of using it directly as I have done above.

accessdbguru
Starting Member

26 Posts

Posted - 2012-08-17 : 11:52:27

Thank you. Please reply with the SQL statement?
Currently I have this:

SELECT COUNT ([check number]) AS TotalChecks,
SUM ([Check Amount]) AS TotalAmount FROM SAccounts
INNER JOIN Checks ON SAccounts.ID = Checks.[Account ID] where Name='PrimerBank' and Checks.[Check Date]='2012-08-13'

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-17 : 12:08:23

[code]SELECT
RIGHT(REPLICATE('0',9) + REPLACE(CAST(COUNT([check number]) AS VARCHAR(32)),'.',''),9) AS TotalChecks,
RIGHT(REPLICATE('0',12) + REPLACE(CAST(CAST(SUM([Check Amount]) AS DECIMAL(19,2)) AS VARCHAR(32)),'.',''),12) AS TotalAmount
FROM
SAccounts
INNER JOIN Checks
ON SAccounts.ID = Checks.[Account ID]
WHERE
NAME = 'PrimerBank'
AND Checks.[Check Date] = '2012-08-13'[/code]

accessdbguru
Starting Member

26 Posts

Posted - 2012-08-17 : 15:33:18

That works like a charm! Thank you

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-17 : 15:52:45

You are very welcome :)

At the risk of sounding like an annoying broken record: more often than not, it is simpler and better to do the formatting at the client/presentation/reporting side.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-09-03 : 10:10:43

quote:
Originally posted by sunitabeck

You are very welcome :)

At the risk of sounding like an annoying broken record: more often than not, it is simpler and better to do the formatting at the client/presentation/reporting side.

You expressed my thoughts

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources