December 18, 2008 at 12:00 pm
Hi guys,
I'm trying to format a decimal value conditionally of its decimals.
Here is an example, it will make it a lot clearer to everyone:
Here are the output I want out of a decimal(9,4) saved in a DB:
Decimal Wanted Output
-25.0000 -25%
15.5000 +15.5%
So, there can always be at most 1 decimal, even though we have more saved in the DB, but if the decimal is 0, we do not want to see it.
We also want to add the plus or minus in front of it, at all times.
Is there a quick format I'm missing in BOL for formating decimals, maybe with convert() or cast(), or will I have to create a UDF to format these decimals?
I got some more weird formatting to do like that, so maybe I will have to write a few UDF to be able to do these!
Thanks in advance,
J-F
Cheers,
J-F
December 18, 2008 at 12:32 pm
Try this:
declare @input decimal(9,4)
set @input = -25.77
select case when @input >0 then '+' else '' end -- gives you a plus sign at the front
+ replace(cast(@input as decimal(5,1)),'.0','')+'%' as [Output]
-- gives one decimal, rounded, except for .0
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
December 18, 2008 at 12:46 pm
Nice one Bob, I like the Replace idea. It's pretty wise.
As I can see, there does not seem to be a built in format to do what I expect to have as output, so I will fit with this nice string trick.
Thanks again Bob.
J-F
Cheers,
J-F
December 18, 2008 at 1:32 pm
You're welcome. Merry Christmas, Jean-François.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply