Sorting a VARCHAR column as FLOAT using the CAST operator isn’t working in MySQL ?

If your cast does not work, then you can use yourColumnName*1 with ORDER BY clause.

Using yourColumnName*1. The syntax is as follows:

SELECT yourColumnName1,yourColumnName2,...N FROM yourTableName ORDER BY yourColumnName*1 DESC;

You can also use CAST() operator. The syntax is as follows:

SELECT yourColumnName1,yourColumnName2,...N FROM yourTableName ORDER BY CAST(yourColumnName as DECIMAL(8,2)) DESC;

To understand the above syntax, let us create a table. The query to create a table is as follows:

mysql> create table VarcharColumnAsFloatDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Amount varchar(20), -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (1.01 sec)

Insert some records in the table using insert command. The query is as follows:

mysql> insert into VarcharColumnAsFloatDemo(Amount) values('3446.23'); Query OK, 1 row affected (0.10 sec) mysql> insert into VarcharColumnAsFloatDemo(Amount) values('2464.46'); Query OK, 1 row affected (0.16 sec) mysql> insert into VarcharColumnAsFloatDemo(Amount) values('6465.78'); Query OK, 1 row affected (0.13 sec) mysql> insert into VarcharColumnAsFloatDemo(Amount) values('6464.98'); Query OK, 1 row affected (0.44 sec) mysql> insert into VarcharColumnAsFloatDemo(Amount) values('645.90'); Query OK, 1 row affected (0.19 sec) mysql> insert into VarcharColumnAsFloatDemo(Amount) values('6465.99'); Query OK, 1 row affected (0.23 sec) mysql> insert into VarcharColumnAsFloatDemo(Amount) values('3745.76'); Query OK, 1 row affected (0.14 sec)

Display all records from the table using select statement. The query is as follows:

mysql> select *from VarcharColumnAsFloatDemo;

The following is the output:

+----+---------+ | Id | Amount | +----+---------+ | 1 | 3446.23 | | 2 | 2464.46 | | 3 | 6465.78 | | 4 | 6464.98 | | 5 | 645.90 | | 6 | 6465.99 | | 7 | 3745.76 | +----+---------+ 7 rows in set (0.00 sec)

Here is the query to sort varchar as float using cast operator:

mysql> select Id,Amount from VarcharColumnAsFloatDemo order by cast(Amount as DECIMAL(8,2)) DESC;

The following is the output:

+----+---------+ | Id | Amount  | +----+---------+ |  6 | 6465.99 | |  3 | 6465.78 | |  4 | 6464.98 | |  7 | 3745.76 | |  1 | 3446.23 | |  2 | 2464.46 | |  5 | 645.90  | +----+---------+ 7 rows in set (0.00 sec)

The second approach is as follows using yourColumnName*1:

mysql> select Id,Amount from VarcharColumnAsFloatDemo order by Amount*1 desc;

The following is the output:

+----+---------+ | Id | Amount | +----+---------+ | 6 | 6465.99 | | 3 | 6465.78 | | 4 | 6464.98 | | 7 | 3745.76 | | 1 | 3446.23 | | 2 | 2464.46 | | 5 | 645.90 | +----+---------+ 7 rows in set (0.00 sec)