Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to ORDER BY last 2 character string in MySQL?
You can use ORDER BY RIGHT() to ORDER BY last 2 character string.
The syntax is as follows
select yourColumnName from yourTableName ORDER BY RIGHT(yourColumnName , 2);
To understand the above syntax, let us create a table. The query to create a table is as follows
mysql> create table OrderByLast2CharactersDemo -> ( -> CustomerId varchar(20), -> CustomerName varchar(20) -> ); Query OK, 0 rows affected (0.58 sec)
Insert some records in the table using insert command. The query is as follows −
mysql> insert into OrderByLast2CharactersDemo(CustomerId,CustomerName) values('John-98','John');
Query OK, 1 row affected (0.20 sec)
mysql> insert into OrderByLast2CharactersDemo(CustomerId,CustomerName) values('Carol-91','Carol');
Query OK, 1 row affected (0.21 sec)
mysql> insert into OrderByLast2CharactersDemo(CustomerId,CustomerName) values('Bob-99','Bob');
Query OK, 1 row affected (0.22 sec)
mysql> insert into OrderByLast2CharactersDemo(CustomerId,CustomerName) values('David-67','David');
Query OK, 1 row affected (0.15 sec)Display all records from the table using select statement. The query is as follows −
mysql> select *from OrderByLast2CharactersDemo;
The following is the output
+------------+--------------+ | CustomerId | CustomerName | +------------+--------------+ | John-98 | John | | Carol-91 | Carol | | Bob-99 | Bob | | David-67 | David | +------------+--------------+ 4 rows in set (0.00 sec)
Here is the query to order by last 2 characters string.
Case 1: The result is in ascending order.
The query is as follows −
mysql> select CustomerId from OrderByLast2CharactersDemo ORDER BY RIGHT(CustomerId , 2);
The following is the output
+------------+ | CustomerId | +------------+ | David-67 | | Carol-91 | | John-98 | | Bob-99 | +------------+ 4 rows in set (0.01 sec)
Case 2 The result is in descending order.
The query is as follows −
mysql> select CustomerId from OrderByLast2CharactersDemo ORDER BY RIGHT(CustomerId , 2) DESC;
The following is the output
+------------+ | CustomerId | +------------+ | Bob-99 | | John-98 | | Carol-91 | | David-67 | +------------+ 4 rows in set (0.00 sec)
Updated on: 30-Jul-2019
2K+ Views
Advertisements