
- Learn MySQL
- MySQL - Home
- MySQL - Introduction
- MySQL - Installation
- MySQL - Administration
- MySQL - PHP Syntax
- MySQL - Connection
- MySQL - Create Database
- MySQL - Drop Database
- MySQL - Select Database
- MySQL - Data Types
- MySQL - Create Tables
- MySQL - Drop Tables
- MySQL - Insert Query
- MySQL - Select Query
- MySQL - Where Clause
- MySQL - Update Query
- MySQL - Delete Query
- MySQL - Like Clause
- MySQL - Sorting Results
- MySQL - Using Join
- MySQL - NULL Values
- MySQL - Regexps
- MySQL - Transactions
- MySQL - Alter Command
- MySQL - Indexes
- MySQL - Temporary Tables
- MySQL - Clone Tables
- MySQL - Database Info
- MySQL - Using Sequences
- MySQL - Handling Duplicates
- MySQL - SQL Injection
- MySQL - Database Export
- MySQL - Database Import
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 Articles
- 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 a VARCHAR column as FLOAT using the CAST operator isn’t working in MySQL ?
- 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?
- 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?
- Use of String.compare for a varchar field in SAP
- Sorting field value (FirstName) for MongoDB?
- Convert varchar to date in MySQL?
- Multiple column sorting in MySQL?
- Perform custom sorting in MySQL
- Convert varchar to unsigned integer in MySQL

Advertisements