Sort Varchar Column as Float Using CAST Operator 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)