MySQL sort string number?

To sort string number, use the CAST() function from MySQL. The syntax is as follows −

SELECT *FROM yourTableName ORDER BY (yourColumnName as Decimal(integerValue,integerValueAfterDecimalPoint)) desc;

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

mysql> create table SortingStringDemo
-> (
-> Amount varchar(10)
-> );
Query OK, 0 rows affected (0.91 sec)

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

mysql> insert into SortingStringDemo values('12.34');
Query OK, 1 row affected (0.21 sec)

mysql> insert into SortingStringDemo values('124.50');
Query OK, 1 row affected (0.56 sec)

mysql> insert into SortingStringDemo values('9.59');
Query OK, 1 row affected (0.20 sec)

mysql> insert into SortingStringDemo values('150.68');
Query OK, 1 row affected (0.17 sec)

mysql> insert into SortingStringDemo values('600.54');
Query OK, 1 row affected (0.11 sec)

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

mysql> select *from SortingStringDemo;

The following is the output.

+--------+
| Amount |
+--------+
| 12.34  |
| 124.50 |
| 9.59   |
| 150.68 |
| 600.54 |
+--------+
5 rows in set (0.00 sec)

Here is the query to sort string number in descending order.

mysql> select *from SortingStringDemo order by cast(Amount as Decimal(6,2)) desc;

The following is the output.

+--------+
| Amount |
+--------+
| 600.54 |
| 150.68 |
| 124.50 |
| 12.34  |
| 9.59   |
+--------+
5 rows in set (0.00 sec)