Recipe2.3.Sorting by Substrings

Recipe 2.3. Sorting by Substrings

Problem

You want to sort the results of a query by specific parts of a string. For example, you want to return employee names and jobs from table EMP and sort by the last two characters in the job field. The result set should look like the following:

 ENAME       JOB ----------  --------- KING        PRESIDENT SMITH       CLERK ADAMS       CLERK JAMES       CLERK MILLER      CLERK JONES       MANAGER CLARK       MANAGER BLAKE       MANAGER ALLEN       SALESMAN MARTIN      SALESMAN WARD        SALESMAN TURNER      SALESMAN SCOTT       ANALYST FORD        ANALYST 

Solution

DB2, MySQL, Oracle, and PostgreSQL

Use the SUBSTR function in the ORDER BY clause:

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

SQL Server

Use the SUBSTRING function in the ORDER BY clause:

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

Discussion

Using your DBMS's substring function, you can easily sort by any part of a string. To sort by the last two characters of a string, find the end of the string (which is the length of the string) and subtract 2. The start position will be the second to last character in the string. You then take all characters after that start position. Because SQL Server requires a third parameter in SUBSTRING to specify the number of characters to take. In this example, any number greater than or equal to 2 will work.