How do I sort numbers saved as VARCHAR in MySQL with some of them having preceding 0 like 085, 090, etc.?



Following is the syntax −

select *from yourTableName order by yourColumnName*1,yourColumnName;

Let us first create a table −

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

Insert some records in the table using insert command −

mysql> insert into DemoTable values('90');
Query OK, 1 row affected (0.20 sec)
mysql> insert into DemoTable values('86');
Query OK, 1 row affected (0.08 sec)
mysql> insert into DemoTable values('45');
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable values('85');
Query OK, 1 row affected (0.08 sec)
mysql> insert into DemoTable values('085');
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable values('090');
Query OK, 1 row affected (0.13 sec)
mysql> insert into DemoTable values('045');
Query OK, 1 row affected (0.10 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable;

This will produce the following output −

+-------+
| Value |
+-------+
|    90 |
|    86 |
|    45 |
|    85 |
|   085 |
|   090 |
|   045 |
+-------+
7 rows in set (0.00 sec)

Following is the query to sort numbers saved as VARCHAR with some of them having to precede 0

mysql> select *from DemoTable order by Value*1,Value;

This will produce the following output −

+-------+
| Value |
+-------+
|   045 |
|    45 |
|   085 |
|    85 |
|    86 |
|   090 |
|    90 |
+-------+
7 rows in set (0.00 sec)

Advertisements