Sorting by Substrings - SQL SERVER

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.

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