Recipe2.4.Sorting Mixed Alphanumeric Data | SQL Cookbook (Cookbooks (OReilly))

Recipe 2.4. 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