Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New to the CF scene
    Join Date
    Oct 2011
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    CONVERT() not converting properly

    I have a table example:

    Code:
    id  -- score
    1   -- 5.66678
    2   -- 11.598
    3 -- 12.2
    4 -- 11.598
    5 -- 2.577777
    score is set as a VARCHAR currently.
    id is an integer

    My simple MySQL query:

    Code:
    SELECT *
    FROM `mysql_table`
    ORDER BY CONVERT( score, DECIMAL ) DESC
    For some reason it pulls it something similar to this:

    Code:
    id  -- score
    2   -- 11.598
    3 -- 12.2
    4 -- 11.598
    1   -- 5.66678
    5 -- 2.577777
    12.2 is stuck between the two 11.598's. Is there a reason for this? Am I doing something wrong?

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,028
    Thanks
    75
    Thanked 4,325 Times in 4,291 Posts
    If I were *guessing*, I'd bet that the first 11.598 value there has some extra characters in the VARCHAR field and so the CONVERT() isn't working right on it.

    To find out, try something like this:
    Code:
    SELECT id, score, CONVERT(score,DECIMAL) AS numScore
    FROM tablename
    ORDER BY numscore DESC
    See if that makes the problem visible...that is, shows that one or more of the CONVERT's don't work.

    If it's only a matter of leading/trailing spaces, just change to
    Code:
    SELECT id, score, CONVERT( TRIM(score), DECIMAL) AS numScore
    FROM tablename
    ORDER BY numscore DESC
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,028
    Thanks
    75
    Thanked 4,325 Times in 4,291 Posts
    LOL! Ignore my first answer! This one is obvious.

    Code:
    mysql> select * from t;
    +------+---------------+
    | id   | s             |
    +------+---------------+
    |    1 | 11.598        |
    |    2 |     11.598    |
    +------+---------------+
    2 rows in set (0.00 sec)
    
    mysql> select id, s, convert(s,DECIMAL) from t;
    +------+---------------+--------------------+
    | id   | s             | convert(s,DECIMAL) |
    +------+---------------+--------------------+
    |    1 | 11.598        |                 12 |
    |    2 |     11.598    |                 12 |
    +------+---------------+--------------------+
    2 rows in set (0.00 sec)
    See? ROUDING!!!

    Now do
    Code:
    mysql> select id, s, convert(s,DECIMAL(20,8)) from t;
    +------+---------------+--------------------------+
    | id   | s             | convert(s,DECIMAL(20,8)) |
    +------+---------------+--------------------------+
    |    1 | 11.598        |              11.59800000 |
    |    2 |     11.598    |              11.59800000 |
    +------+---------------+--------------------------+
    2 rows in set (0.00 sec)
    Enough said?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •