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

Updated on 30-Jun-2020 09:58:33

• Related Questions & Answers
• How to sort an alphanumeric column with different lengths in MySQL?
• How to order an alphanumeric column in MySQL?
• How to do alphanumeric sort in MongoDB?
• Sort only numbers from alphanumeric string in MySQL?
• Sort strings in Alphanumeric sequence
• How to sort mixed numeric/alphanumeric array in JavaScript
• How to sort more than one column at a time in MySQL?
• How to sort a numerical factor column in an R data frame?
• How to get the maximum value from a column with alphanumeric strings beginning with specific characters in MYSQL?
• Delete duplicate alphanumeric entries from column data in SQL
• Sort data column to retrieve max textual value in MySQL
• How to sort an R data frame column without losing row names?
• How to remove all non-alphanumeric characters from a string in MySQL?
• Sort a column ignoring a specific word in MySQL
• How to rename a column in an existing MySQL table?