SQL Server - Sorting Mixed Alphanumeric Data

Problem

You have mixed alphanumeric data and want to sort by either the numeric or character portion of the data. Consider this view:

 
 create view V
 as
 select ename||' '||deptno as data
   from emp

 select * from V

 DATA
 -------------
 SMITH 20
 ALLEN 30
 WARD 30
 JONES 20
 MARTIN 30
 BLAKE 30
 CLARK 10
 SCOTT 20
 KING 10
 TURNER 30
 ADAMS 20
 JAMES 30
 FORD 20
 MILLER 10

You want to sort the results by DEPTNO or ENAME. Sorting by DEPTNO produces the following result set:

 DATA
 ----------
 CLARK 10
 KING 10
 MILLER 10
 SMITH 20
 ADAMS 20
 FORD 20
 SCOTT 20
 JONES 20
 ALLEN 30
 BLAKE 30
 MARTIN 30
 JAMES 30
 TURNER 30
 WARD 30

Sorting by ENAME produces the following result set:

 DATA
 ---------
 ADAMS 20
 ALLEN 30
 BLAKE 30
 CLARK 10
 FORD 20
 JAMES 30
 JONES 20
 KING 10
 MARTIN 30
 MILLER 10
 SCOTT 20
 SMITH 20
 TURNER 30
 WARD 30

Solution

Oracle and PostgreSQL

Use the functions REPLACE and TRANSLATE to modify the string for sorting:

 /* ORDER BY DEPTNO */

 1 select data
 2   from V
 3  order by replace(data,
 4           replace(
 5         translate(data,'0123456789','##########'),'#',''),'')

 /* ORDER BY ENAME */

 1 select data
 2   from emp
 3  order by replace(
 4           translate(data,'0123456789','##########'),'#','')

DB2

Implicit type conversion is more strict in DB2 than in Oracle or PostgreSQL, so you will need to cast DEPTNO to a CHAR for view V to be valid. Rather than recreate view V, this solution will simply use an inline view. The solution uses REPLACE and TRANSLATE in the same way as the Oracle and PostrgreSQL solution, but the order of arguments for TRANSLATE is slightly different for DB2:

 /* ORDER BY DEPTNO */

 1  select *
 2    from (
 3  select ename||' '||cast(deptno as char(2)) as data
 4    from emp
 5         ) v
 6   order by replace(data,
 7             replace(
 8           translate(data,'##########','0123456789'),'#',''),'')

 /* ORDER BY ENAME */

 1  select *
 2    from (
 3  select ename||' '||cast(deptno as char(2)) as data
 4    from emp
 5         ) v
 6   order by replace(
 7            translate(data,'##########','0123456789'),'#','')

MySQL and SQL Server

The TRANSLATE function is not currently supported by these platforms, thus a solution for this problem will not be provided.

Discussion

The TRANSLATE and REPLACE functions remove either the numbers or characters from each row, allowing you to easily sort by one or the other. The values passed to ORDER BY are shown in the following query results (using the Oracle solution as the example, as the same technique applies to all three vendors; only the order of parameters passed to TRANSLATE is what sets DB2 apart):

 
 select data,
        replace(data,
        replace(
      translate(data,'0123456789','##########'),'#',''),'') nums,
        replace(
      translate(data,'0123456789','##########'),'#','') chars
   from V

 DATA         NUMS   CHARS
 ------------ ------ ----------
 SMITH 20     20     SMITH
 ALLEN 30     30     ALLEN
 WARD 30      30     WARD
 JONES 20     20     JONES
 MARTIN 30    30     MARTIN
 BLAKE 30     30     BLAKE
 CLARK 10     10     CLARK
 SCOTT 20     20     SCOTT
 KING 10      10     KING
 TURNER 30    30     TURNER
 ADAMS 20     20     ADAMS
 JAMES 30     30     JAMES
 FORD 20      20     FORD
 MILLER 10    10     MILLER

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