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 :


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 varchar(40) Insted of that you can use uuid()

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));

Update #1

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