How to order by certain part of a string in MySQL?

You can use ORDER BY SUBSTRING() to order by certain part of a string in MySQL. Let us first create a table:

mysql> create table DemoTable (UserId varchar(200));
Query OK, 0 rows affected (0.68 sec)

Following is the query to insert records in the table using insert command:

mysql> insert into DemoTable values('USER_1234');
Query OK, 1 row affected (0.13 sec)
mysql> insert into DemoTable values('USER_John');
Query OK, 1 row affected (0.19 sec)
mysql> insert into DemoTable values('USER_Sam');
Query OK, 1 row affected (0.22 sec)
mysql> insert into DemoTable values('USER_Carol');
Query OK, 1 row affected (0.16 sec)
mysql> insert into DemoTable values('USER_Bob');
Query OK, 1 row affected (0.14 sec)

Following is the query to display records from the table using select command:

mysql> select *from DemoTable;

This will produce the following output:

+------------+
| UserId     | 
+------------+
| USER_1234  |
| USER_John  |
| USER_Sam   |
| USER_Carol |
| USER_Bob   |
+------------+
5 rows in set (0.00 sec)

Case 1: When you want to ORDER BY part of a string in ascending order.

Following is the query. Here, we will order certain part of the string after 4th character:

mysql> select *from DemoTable order by substring(UserId,4) asc;

This will produce the following output

+------------+
| UserId     |
+------------+
| USER_1234  |
| USER_Bob   |
| USER_Carol |
| USER_John  |
| USER_Sam   |
+------------+
5 rows in set (0.00 sec)

Case 2: When you want to ORDER BY part of a string in descending order. Following is the query:

mysql> select *from DemoTable order by substring(UserId,4) desc;

This will produce the following output. Here, we will order certain part of the string after 4th character:

+------------+
| UserId     |
+------------+
| USER_Sam   |
| USER_John  |
| USER_Carol |
| USER_Bob   |
| USER_1234  |
+------------+
5 rows in set (0.00 sec)