...

View Full Version : CONVERT() not converting properly



PHPGator
10-27-2011, 08:37 PM
I have a table example:


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:


SELECT *
FROM `mysql_table`
ORDER BY CONVERT( score, DECIMAL ) DESC

For some reason it pulls it something similar to this:


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?

Old Pedant
10-27-2011, 09:08 PM
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:


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


SELECT id, score, CONVERT( TRIM(score), DECIMAL) AS numScore
FROM tablename
ORDER BY numscore DESC

Old Pedant
10-27-2011, 09:11 PM
LOL! Ignore my first answer! This one is obvious.



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

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?



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum