would like to format a decimal number as percent

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

SQL Server 2000 Forums

Transact-SQL (2000)

would like to format a decimal number as percent

Author

Topic

michaelxvo
Starting Member

47 Posts

Posted - 2006-12-08 : 19:14:00

I would like to format a decimal number (0.1) to look like this 10%.
What should I do? is there any format function that I can use?

Thank you

pootle_flump

1064 Posts

Posted - 2006-12-08 : 19:33:41

Well - do it in front end would be the standard response. Otherwise:

CAST(CAST(ROUND(MyDecimal * 100, 0) AS TinyInt) AS VarChar(3)) + '%'

Seems rather long winded but I think it is all necessary (assuming you can have decimals of over 2 decimal places).

michaelxvo
Starting Member

47 Posts

Posted - 2006-12-08 : 23:02:22

quote:
Originally posted by pootle_flump

Well - do it in front end would be the standard response. Otherwise:
CAST(CAST(ROUND(MyDecimal * 100, 0) AS TinyInt) AS VarChar(3)) + '%'
Seems rather long winded but I think it is all necessary (assuming you can have decimals of over 2 decimal places).

Thank you for your response.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-09 : 03:21:02

<<
do it in front end would be the standard response
>>
Yes

Michael, where do you want to show data?

Madhivanan

Failing to plan is Planning to fail

michaelxvo
Starting Member

47 Posts

Posted - 2006-12-09 : 11:47:14

quote:
Originally posted by madhivanan

<<
do it in front end would be the standard response
>>
Yes
Michael, where do you want to show data?

Madhivanan

Failing to plan is Planning to fail

I want to create a view. After that I want to export to a text file.

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2006-12-09 : 16:15:12

SELECT STR(0.1*100,3)+'%'

--Jeff Moden

michaelxvo
Starting Member

47 Posts

Posted - 2006-12-11 : 14:19:43

quote:
Originally posted by Jeff Moden

SELECT STR(0.1*100,3)+'%'

--Jeff Moden

Thank you for your code. simple and easy

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-11 : 14:24:00

If you want to remove leading spaces, use Jeff's suggestion with a twist.
SELECT LTRIM(STR(0.1 * 100, 3)) + '%'

STR also does rounding. 10.5% turns into 11%.

Peter Larsson
Helsingborg, Sweden

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2006-12-11 : 20:03:47

That's one of the things I like about STR... automatic right justification...

And, it's the only thing in SQL Server that will automatically do "bankers rounding", as well.

--Jeff Moden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources