How to sort varchar fields numerically in MySQL?

'LPAD(lower(column_name))' is used to sort varchar fields numerically in MySQL. Let's 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)
　　　-> );

Records are inserted with the help of the INSERT command.

mysql> insert into SortingvarcharDemo values("99);
mysql> insert into SortingvarcharDemo values("9);
mysql> insert into SortingvarcharDemo values("199);
mysql> insert into SortingvarcharDemo values("1);
mysql> insert into SortingvarcharDemo values("999);
mysql> insert into SortingvarcharDemo values("78);

To display records, use the select command.

mysql> select　*from SortingvarcharDemo;

This is the output.

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

The list is unordered in the output above-It is neither ascending nor descending.

To sort the numbers in ascending or descending order, use the following 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;

This is the output.

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

The results are sorted in ascending order above.