Recipe6.8.Ordering by Parts of a String

Recipe 6.8. Ordering by Parts of a String

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.