Recipe 2.3. Sorting by SubstringsProblemYou 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 SolutionDB2, MySQL, Oracle, and PostgreSQLUse the SUBSTR function in the ORDER BY clause: select ename,job from emp order by substr(job,length(job)-2) SQL ServerUse the SUBSTRING function in the ORDER BY clause: select ename,job from emp order by substring(job,len(job)-2,2) DiscussionUsing 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. |