Ordering by Parts of a String : SQL

Problem

You want to order your result set based on a substring. Consider the following records:

 ENAME
 ----------
 SMITH
 ALLEN
 WARD
 JONES
 MARTIN
 BLAKE
 CLARK
 SCOTT
 KING
 TURNER
 ADAMS
 JAMES
 FORD
 MILLER

You want the records to be ordered based on the last two characters of each name:

 ENAME
 ---------
 ALLEN
 TURNER
 MILLER
 JONES
 JAMES
 MARTIN
 BLAKE
 ADAMS
 KING
 WARD
 FORD
 CLARK
 SMITH
 SCOTT

Solution

The key to this solution is to find and use your DBMS's built-in function to extract the substring on which you wish to sort. This is typically done with the SUBSTR function.

DB2, Oracle, MySQL, and PostgreSQL

Use a combination of the built-in functions LENGTH and SUBSTR to order by a specific part of a string:

 1 select ename
 2   from emp
 3  order by substr(ename,length(ename)-1,)

SQL Server

Use functions SUBSTRING and LEN to order by a specific part of a string:

 1 select ename
 2   from emp
 3  order by substring(ename,len(ename)-1,2)

Discussion

By using a SUBSTR expression in your ORDER BY clause, you can pick any part of a string to use in ordering a result set. You're not limited to SUBSTR either. You can order rows by the result of almost any expression.

** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table