Format values as Percent

  • Hi ALL,

    SQL:

    Select MyColumn 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 1 asc

    Result:

    MyColumn

    1.10%

    10.00%

    12.00%

    2.00%

    Problem: values are not sorted

    Question: Is it possible to sort values in ascending Order?

    Thanks

  • It is interesting it doesn't work with ROW_NUMBER() function , so how it works on the other way!? :w00t:

    ============================================================
    SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
    http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

  • They are sorted, they're just sorted as strings which is what the column is by the point the order by is evaluated. Perhaps something like this:

    Select CAST(CAST(BaseColumn*100 AS numeric(10,2)) AS varchar(5)) + '%' as MyColumn FROM

    (

    SELECT 0.10 as BaseColumn

    Union

    SELECT 0.011 as BaseColumn

    Union

    SELECT 0.02 as BaseColumn

    Union

    SELECT 0.12 as BaseColumn

    ) MyTable

    Order by BaseColumn asc

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Gila you are very SQL Sick... very nice solution without adding or removing any other expression!

    Nice solution Gila!

    Dugi

    :w00t:

    ============================================================
    SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
    http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

  • Well I am expecting result in following order:

    1.10%

    2.00%

    10.00%

    12.00%

  • I find it on another way like this but Gila's post is the best for me!?

    Select MyColumn 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

    ============================================================
    SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
    http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

  • SMAZ (8/26/2008)


    Well I am expecting result in following order:

    1.10%

    2.00%

    10.00%

    12.00%

    Which is exactly the order my query returns them in. Did you try it?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thanks Gail!

    Its perfect.

  • SMAZ (8/26/2008)


    Thanks Gail!

    Its perfect.

    He is perfect also in other solutions as I can see ...!

    :hehe::hehe::hehe:

    ============================================================
    SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
    http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

  • One more querstion on thsi:

    Now the result is as follow:

    1.10%

    2.00%

    10.00%

    12.00%

    Is it possible to implement some formatting on that values like:

    1.10% = 01.10%

    2.00% = 02.00%

    10.00% = 10.00%

    12.00% = 12.00%

    Thanks

  • 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

    _____________________________________________
    [font="Comic Sans MS"]Quatrei Quorizawa[/font]
    :):D:P;):w00t::cool::hehe:
    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
  • 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 ^__^

    _____________________________________________
    [font="Comic Sans MS"]Quatrei Quorizawa[/font]
    :):D:P;):w00t::cool::hehe:
    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
  • 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

    _____________________________________________
    [font="Comic Sans MS"]Quatrei Quorizawa[/font]
    :):D:P;):w00t::cool::hehe:
    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
  • 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 !!!

  • AAAAAAAAW... Sorry about that gila. Didn't see the picture perfectly

    sorry for bad english

    _____________________________________________
    [font="Comic Sans MS"]Quatrei Quorizawa[/font]
    :):D:P;):w00t::cool::hehe:
    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

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply