Change decimal to percent 1

[pmcdaniel]

I need to convert a decimal to a percent. Here is what I have:

DECLARE @MonetaryValue  NUMERIC(10, 3),
        @ReturnValue    VARCHAR(5),
        @pValue VARCHAR(7)

SET @pValue = '.125'

  SET @MonetaryValue = CAST(@pValue AS NUMERIC(10, 3))
  SET @ReturnValue = CAST(CONVERT(INT, 100 * @MonetaryValue) AS VARCHAR(5)) + '%'

SELECT @ReturnValue

When I make @pValue equal to any of the below I get the correct percent except for .125 where I get 12%. What I want is 12.5%. Can someone please help? I've tried other ways but I'm sure there are easier ways then the ones I found and I want this to be done right.

'1' = 100%
'.25' = 25%
'.125' = 12%
'.05' = 5%

 

[gmmastros]

Here's your problem:

CONVERT(INT, 100 * @MonetaryValue)

When you multiply 0.125 by 100, you get 12.5, but then you cast it to an int, which removes the fractional component.

If you convert to Decimal(12,1) you will preserve the decimal point, but then you will also get .0 sometimes too. You can remove the .0 part (when it evaluates to a whole number) by replacing .0 with empty string.

Ex:

DECLARE @MonetaryValue  NUMERIC(10, 3),
        @ReturnValue    VARCHAR(5),
        @pValue VARCHAR(7)

SET @pValue = '.12'

  SET @MonetaryValue = CAST(@pValue AS NUMERIC(10, 3))
  SET @ReturnValue = Replace(CAST(CAST(100 * @MonetaryValue As Decimal(12,1)) AS VARCHAR(5)), '.0','') + '%'

SELECT @ReturnValue, @MonetaryValue

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[pmcdaniel]

Excellent! I had something similar to what you mentioned but then I screwed it up for '1'. Your code, however, has that covered!

Thanks much gmmastros!

 

[JeffModen]

First, the variable "@ReturnValue" smacks of the creation of a UDF. Skip that if you can because most UDF's are just bad for performance. With that in mind, let's say you have a table of VARCHAR percentages (and having them as VARCHAR is also a mistake because you need to convert them to do math with them). You don't need a real complex formula or the performance overhead of a UDF to do what you want (although I'd recommend you do this type of formatting in the GUI instead).

 SELECT STR(CAST(SomeDecimalString AS DECIMAL(10,3))*100,8,1)+'%'
   FROM (--==== This simulates what might come from a table
         SELECT '1' SomeDecimalString UNION ALL
         SELECT '.25' UNION ALL
         SELECT '.125' UNION ALL
         SELECT '5'
        ) AS TestTable

The neat thing about this is that it will align the decimal places, as well. I mean if you're going to format it, go ALL the way. ;-)

--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row

 

[pmcdaniel]

Yes, it should be done on the GUI but we receive data listed at field1, field2, field3, etc... as our column names. I'm trying to make it as dynamic as possible on the GUI without having to continually update the GUI code. So I am doing all the shaping on the SQL side and then simply have a loop on the GUI side.

And yes the code is being used in a UDF.

Some day it's all supposed to be fixed but this is the way I'm forced to do it for now.

Thanks for the reply/advice and I'll try your code.

 

[JeffModen]

Why not just use the forumula I gave you instead of creating a UDF? What else does the UDF do?

--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row

 

[Emtucifor]

Jeff, do you have a version that gives the result the original poster asked for? Your version still has instances of '.0'.

 

[JeffModen]

Sure... and it still aligns the decimal places...

SELECT REPLACE(STR(CAST(SomeDecimalString AS DECIMAL(10,3))*100,8,1)+'%','.0','')
FROM (--==== This simulates what might come from a table
SELECT '1' SomeDecimalString UNION ALL
SELECT '.25' UNION ALL
SELECT '.125' UNION ALL
SELECT '5'
) AS TestTable

How 'bout yourself? ;-)

--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row