Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Format values as Percent Expand / Collapse
Author
Message
Posted Thursday, August 28, 2008 7:13 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 27, 2014 12:19 AM
Points: 177, Visits: 456
Hi there,

If your already using the code given byu Gila Monster (:DYOUR THE MAN!), you can add this...

Yeah I know the code I added looks icky but hope it helps

Select NewColumn=CASE
WHEN CAST(LEFT(MyColumn,Len(MyColumn)-1)AS DECIMAL(5,2))<10 THEN '0'+CAST(MyColumn AS VARCHAR(50))
ELSE MyColumn
END
FROM
(
SELECT CAST(CAST(0.10*100 AS numeric(10,2)) AS varchar(5)) + '%' as MyColumn
Union
SELECT CAST(CAST(0.011*100 AS numeric(10,2)) AS varchar(5)) + '%' as MyColumn
Union
SELECT CAST(CAST(0.02*100 AS numeric(10,2)) AS varchar(5)) + '%' as MyColumn
Union
SELECT CAST(CAST(0.12*100 AS numeric(10,2)) AS varchar(5)) + '%' as MyColumn
) MyTable
Order by CAST(Replace(MyColumn,'%','') as DECIMAL) asc




_____________________________________________
Quatrei Quorizawa
:):D:P;)
MABUHAY PHILIPPINES!

"Press any key...
Where the heck is the any key?
hmmm... Let's see... there's ESC, CTRL, Page Up...
but no any key"
- Homer Simpson
Post #560934
Posted Thursday, August 28, 2008 7:25 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 27, 2014 12:19 AM
Points: 177, Visits: 456
Hi there... agian,

Remember the icky code I added to Gila Monster and Dugi's code? We'll I made it a less icky but if your already using Gila's code, you need a few modifications for this one

Select NewColumn=CASE
WHEN MyColumn<10 THEN '0'+CAST(MyColumn AS VARCHAR(50))+'%'
ELSE CAST(MyColumn AS VARCHAR(50)) +'%'
END
FROM
(
SELECT CAST(0.10*100 AS numeric(10,2)) as MyColumn
Union
SELECT CAST(0.011*100 AS numeric(10,2)) as MyColumn
Union
SELECT CAST(0.02*100 AS numeric(10,2)) as MyColumn
Union
SELECT CAST(0.12*100 AS numeric(10,2)) as MyColumn
) MyTable
Order by CAST(Replace(MyColumn,'%','') as DECIMAL) asc

Oh... and sorry for my bad English, Hope my code also helps ^__^


_____________________________________________
Quatrei Quorizawa
:):D:P;)
MABUHAY PHILIPPINES!

"Press any key...
Where the heck is the any key?
hmmm... Let's see... there's ESC, CTRL, Page Up...
but no any key"
- Homer Simpson
Post #560944
Posted Thursday, August 28, 2008 7:38 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 27, 2014 12:19 AM
Points: 177, Visits: 456
Hi there... again,

Heres another less icky way

Select RIGHT('00'+CAST(MyColumn AS VARCHAR(50))+'%',6)
FROM
(
SELECT CAST(0.10*100 AS numeric(10,2)) as MyColumn
Union
SELECT CAST(0.011*100 AS numeric(10,2)) as MyColumn
Union
SELECT CAST(0.02*100 AS numeric(10,2)) as MyColumn
Union
SELECT CAST(0.12*100 AS numeric(10,2)) as MyColumn
) MyTable
Order by CAST(Replace(MyColumn,'%','') as DECIMAL) asc

Although if you use this code, make sure that you won't have a data with0 '100%' or 1.00 casue that would ruuin the could like so

Select RIGHT('00'+CAST(MyColumn AS VARCHAR(50))+'%',6)
FROM
(
SELECT CAST(1.00*100 AS numeric(10,2)) as MyColumn
) MyTable
Order by CAST(Replace(MyColumn,'%','') as DECIMAL) asc

Hope this also helps


_____________________________________________
Quatrei Quorizawa
:):D:P;)
MABUHAY PHILIPPINES!

"Press any key...
Where the heck is the any key?
hmmm... Let's see... there's ESC, CTRL, Page Up...
but no any key"
- Homer Simpson
Post #560947
Posted Friday, August 29, 2008 5:57 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 4, 2014 2:33 AM
Points: 1,178, Visits: 2,644
Quatrei.X (8/28/2008)
Hi there,

If your already using the code given byu Gila Monster (:DYOUR THE MAN!), you can add this...



By the way, Gila Monster is female !!!



--
Nigel
Useful Links:
How to post data/code on a forum to get the best help
The "Numbers" or "Tally" Table - Jeff Moden

Post #561131
Posted Friday, September 12, 2008 1:52 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 27, 2014 12:19 AM
Points: 177, Visits: 456
AAAAAAAAW... Sorry about that gila. Didn't see the picture perfectly

sorry for bad english


_____________________________________________
Quatrei Quorizawa
:):D:P;)
MABUHAY PHILIPPINES!

"Press any key...
Where the heck is the any key?
hmmm... Let's see... there's ESC, CTRL, Page Up...
but no any key"
- Homer Simpson
Post #568317
Posted Thursday, April 18, 2013 5:35 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 8, 2013 7:16 PM
Points: 221, Visits: 132
Don't know if you are still looking for an answer, but this should work for you:

Select MyColumn FROM
(
SELECT right(replicate('0',5) +CAST(CAST(0.10*100 AS numeric(10,2)) AS varchar(5)), 5) + '%' as MyColumn
Union
SELECT right(replicate('0',5) +CAST(CAST(0.011*100 AS numeric(10,2)) AS varchar(5)), 5) + '%' as MyColumn
Union
SELECT right(replicate('0',5) +CAST(CAST(0.02*100 AS numeric(10,2)) AS varchar(5)), 5) + '%' as MyColumn
Union
SELECT right(replicate('0',5) +CAST(CAST(0.12*100 AS numeric(10,2)) AS varchar(5)), 5) + '%' as MyColumn
) MyTable
Order by CAST(Replace(MyColumn,'%','') as DECIMAL) asc


--------
For SQL Tips, check out my blog: http://lantztechknowledge.blogspot.com/
You can also follow my twitter account to get daily updates: @BLantz2455
Post #1444194
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse