How to sort an alphanumeric column in MySQL?

To sort an alphanumeric column, use LIKE operator along with SUBSTRING(). Let us first create a table −

mysql> create table DemoTable
   -> (
   -> StudentId varchar(100)
   -> );
Query OK, 0 rows affected (1.54 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable values('S/TU/100');
Query OK, 1 row affected (0.28 sec)

mysql> insert into DemoTable values('S/TU/1000');
Query OK, 1 row affected (0.22 sec)

mysql> insert into DemoTable values('S/TU/10');
Query OK, 1 row affected (0.47 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable;

Output

This will produce the following output −

+-----------+
| StudentId |
+-----------+
| S/TU/100  |
| S/TU/1000 |
| S/TU/10   |
+-----------+
3 rows in set (0.00 sec)

Here is the query to sort an alphanumeric column in MySQL −

mysql> select StudentId from DemoTable where StudentId LIKE 'S%' order by
substring(StudentId,1,9), substring(StudentId,10)*1;

Output

This will produce the following output −

+-----------+
| StudentId |
+-----------+
| S/TU/10   |
| S/TU/100  |
| S/TU/1000 |
+-----------+
3 rows in set (0.00 sec)

Karthikeya Boyini

Published on 10-Jul-2019 10:10:53

Previous Page Print Page