float - mysql convert varchar to int
Sorting a VARCHAR column as FLOAT using the CAST operator don't work in MySQL (4)
I can't find a way to sort a varchar column casted as float. Here is my SQL request:
SELECT guid, number FROM table ORDER BY 'CAST(number AS FLOAT) DESC'
The "number" column is defined like this:
number varchar(20) ascii_general_ci
And the values defined in this column for my test are :
0.00 200.00 20.00 100.00
MySQL totally ignore the CAST operator and sort the columns by guid...
Is there a bug in MySQL or did I do something wrong ?
If you have taken GUID then the size should be
Insted of that you can use
I have done it using
select uuid(), number order by 'cast(number as float) desc';
It's working fine. If this is not you want can you send your entire code?
Try this trick (helps to sort strings as numbers)-
SELECT guid, number FROM table ORDER BY number * 1 DESC
It will help MySQL to cast string to number.
Another solution -
...CAST(value as DECIMAL(10,5))
Difficulty in showing data in ascending order from MySQL
This problem occures because, probably, you store the lead_price not as a integer or float (better is a integer option), but as a string. And it's compared as a string - so by char by char.
The best solution - change the column type to integer and store there a price * 100 (when you've got £1050,50 you should store 105050 in database (all programming langages where you're using floats have a huge problem with rounding prices), and the currency in a new column.
Or, not so good solution,
ORDER BY CAST(Lead_Price AS DECIMAL(10,2)); , but it doesn't perform well.
Sorting data which include integer and multi points in varchar in MySQL
I don't have mySQL installed so I cannot test. Test casting the varchar into decimal before sorting.
it should go as follow : SELECT * FROM table ORDER by CAST(t_nmAS DECIMAL(10,6));
Ok then explode it in an array with '." as the delemiter as integer
ex : SELECT * FROM table ORDER BY string_to_array(t_nmAS , '.')::int ASC