Sorting varchar field numerically in MySQL?

?LPAD(lower(column_name))' is used to sort the varchar field numerically in MySQL. Let us see an example.

Firstly, we will create a table. The CREATE command is used to create a table.

 mysql> create table SortingvarcharDemo -> ( -> List varchar(10) -> ); Query OK, 0 rows affected (0.82 sec) 

Records are inserted with the help of INSERT command.

 mysql> insert into SortingvarcharDemo values("99"); Query OK, 1 row affected (0.12 sec) mysql> insert into SortingvarcharDemo values("9"); Query OK, 1 row affected (0.17 sec) mysql> insert into SortingvarcharDemo values("199"); Query OK, 1 row affected (0.19 sec) mysql> insert into SortingvarcharDemo values("1"); Query OK, 1 row affected (0.11 sec) mysql> insert into SortingvarcharDemo values("999"); Query OK, 1 row affected (0.10 sec) mysql> insert into SortingvarcharDemo values("78"); Query OK, 1 row affected (0.17 sec) 

To display the records, use the select command.

 mysql> select *from SortingvarcharDemo; 

Here is the output.

 +------+ | List | +------+ | 99 | | 9 | | 199 | | 1 | | 999 | | 78 | +------+ 6 rows in set (0.05 sec) 

In the above output, the list is unordered- it is neither in ascending order nor in descending order.

To numerically sort it in ascending order or descending order, here is the syntax.

 SELECT * FROM yourTableName ORDER BY LPAD(lower(Column_name), value1,values2) asc; 

The following is the query.

 mysql> SELECT * FROM SortingvarcharDemo ORDER BY LPAD(lower(List), 6,0) asc; 

Here is the output.

 +------+ | List | +------+ | 1 | | 9 | | 78 | | 99 | | 199 | | 999 | +------+ 6 rows in set (0.17 sec) 

In the above, the result is sorted in ascending order.