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.

Ankith Reddy

Published on 20-Nov-2018 12:04:34