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.

• Related Questions & Answers
• Can we compare numbers in a MySQL varchar field?
• Set numbers as a varchar field and compare in MySQL
• Getting the maximum value from a varchar field in MySQL
• Searching for an integer value in a varchar field in MySQL?
• Sorting field value (FirstName) for MongoDB?
• MySQL query to get the max value with numeric values in varchar field?
• Count the number of occurrences of a string in a VARCHAR field in MySQL?
• Sorting a VARCHAR column as FLOAT using the CAST operator isn’t working in MySQL ?
• Use of String.compare for a varchar field in SAP
• Is there a way to convert Integer field into Varchar without losing data in MySQL?
• Will extending the size of a varchar field in MySQL affect the data inside it?
• Convert varchar to date in MySQL?
• Multiple column sorting in MySQL?
• Perform custom sorting in MySQL
• Convert string (varchar) to double in MySQL