Is it possible to sort varchar data in ascending order that have both string and number values with MySQL?

MySQLMySQLi Database

For this, you can use ORDER BY IF(CAST()). Let us first create a table −

mysql> create table DemoTable(EmployeeCode varchar(100));
Query OK, 0 rows affected (1.17 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable values('190');
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable values('100');
Query OK, 1 row affected (0.30 sec)
mysql> insert into DemoTable values('John');
Query OK, 1 row affected (0.23 sec)
mysql> insert into DemoTable values('120');
Query OK, 1 row affected (0.21 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable;

This will produce the following output −

+--------------+
| EmployeeCode |
+--------------+
| 190          |
| 100          |
| John         |
| 120          |
+--------------+
4 rows in set (0.00 sec)

Following is the query to sort varchar data in ascending order with both string and number values −

mysql> select *from DemoTable
ORDER BY IF(CAST(EmployeeCode AS SIGNED) = 0, 100000000000, CAST(EmployeeCode AS SIGNED));

This will produce the following output. Here, numbers get sorted first −

+--------------+
| EmployeeCode |
+--------------+
| 100          |
| 120          |
| 190          |
| John         |
+--------------+
4 rows in set, 1 warning (0.00 sec)
raja
Published on 22-Aug-2019 07:17:28
Advertisements