Formatting numbers - SQL Server Forums

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

Formatting numbers

Author

Topic

Sean Frost
Starting Member

19 Posts

Posted - 2010-02-14 : 07:24:36

In a table, I have a column which has floats such as

I want to format this into an 8-character string with 3 decimal places. For the examples above, I want to get

I tried casting as varchar, padding zeros based on the length etc., and it is growing messy and above my pay grade. Any help or suggestions much appreciated.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-14 : 09:03:10

[code]
select right('000000000'+convert(varchar(9),convert(int,test*1000)),9) as test from
(
select 21.0 as test union all
select 17.5 union all
select 3.0 union all
select 0.5
)dt

-- gives
test
---------
000021000
000017500
000003000
000000500
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

Sean Frost
Starting Member

19 Posts

Posted - 2010-02-14 : 10:14:09

THAT is elegant and simple! Thank you webfred. I would post my method which sort of worked, but I am ashamed

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-14 : 14:37:25

Don't be ashamed.
I am sure that my solution is not the best.

No, you're never too old to Yak'n'Roll if you're too young to die.

Kristen
Test

22859 Posts

Posted - 2010-02-14 : 16:05:04

Is Style=9 a typo in:

convert(varchar(9), SomeInt, 9)

??

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-15 : 02:07:01

quote:
Originally posted by Kristen

Is Style=9 a typo in:

convert(varchar(9), SomeInt, 9)

??

You overlooked it. It is actually part of RIGHT function

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-15 : 02:09:51

Another method would be

select replace(str(replace(test*1000,'.','')),' ','0') test from
(
select 21.0 as test union all
select 17.5 union all
select 3.0 union all
select 0.5
)dt

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-02-15 : 02:09:54

So it is, thanks for pointing that out.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-15 : 02:11:58

quote:
Originally posted by Kristen

So it is, thanks for pointing that out.

No Problem

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources