Sort Varchar Numeric Columns in MySQL

Let us first create a table −

mysql> create table DemoTable726 (Value varchar(100));
Query OK, 0 rows affected (0.60 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable726 values('100');
Query OK, 1 row affected (0.18 sec)
mysql> insert into DemoTable726 values('10');
Query OK, 1 row affected (0.15 sec)
mysql> insert into DemoTable726 values('110');
Query OK, 1 row affected (0.17 sec)
mysql> insert into DemoTable726 values('2000');
Query OK, 1 row affected (0.18 sec)
mysql> insert into DemoTable726 values('1000');
Query OK, 1 row affected (0.14 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable726;

This will produce the following output -

+-------+
| Value |
+-------+
| 100   |
| 10    |
| 110   |
| 2000  |
| 1000  |
+-------+
5 rows in set (0.00 sec)

Following is the query to sort varchar numeric columns in ascending order −

mysql> select *from DemoTable726 order by CAST(Value AS DECIMAL(20,2));

This will produce the following output -

+-------+
| Value |
+-------+
| 10    |
| 100   |
| 110   |
| 1000  |
| 2000  |
+-------+
5 rows in set (0.00 sec)

Following is the query to sort and display the result in descending order −

mysql> select *from DemoTable726 order by CAST(Value AS DECIMAL(20,2)) DESC;

This will produce the following output -

+-------+
| Value |
+-------+
| 2000  |
| 1000  |
| 110   |
| 100   |
| 10    |
+-------+
5 rows in set (0.00 sec)