Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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.
Updated on: 30-Jul-2019
2K+ Views
Advertisements