2.4 对字母数字混合的数据排序

2.4 对字母数字混合的数据排序
Q: 现有字母和数字混合的数据，按照数字或字母部分来排序，建立如下视图:

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
(14 rows)

分别实现对 data 的字母和数字排序

A: 使用函数 replace(), translate()

Oracle, PostgreSQL
/* order by deptno */
select data
from V
order by replace(data,
replace(
translate(data, '0123456789', '##########'), '#', ''), '')
data
-----------
MILLER 10
CLARK 10
KING 10
SCOTT 20
JONES 20
SMITH 20
ADAMS 20
FORD 20
WARD 30
TURNER 30
ALLEN 30
BLAKE 30
MARTIN 30
JAMES 30
(14 rows)

/* order by ename */
select data
from V
order by replace(
translate(data, '0123456789', '##########'), '#', '')

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
(14 rows)

DB2 (隐式类型转换严格，为使视图有效，需要将 deptno 转换成 char 类型)
/* order by deptno */
select *
from (
select ename ||' '|| cast(deptno as char(2)) as data
from emp
) V
order by replace(data,
replace(
translate(data, '##########', '0123456789'), '#', ''), '')

/* order by ename */
select *
from (
select ename ||' '|| cast(deptno as char(2)) as data
from emp
) V
order by replace(
translate(data, '##########', '0123456789'), '#', '')

MySQL, SQL Server (不支持 translate(), 无解决方案)

Tables Used:
for MySQL
http://www.hooto.com/home/rui/doc/archives/5089.html
for PostgreSQL
http://www.hooto.com/home/rui/doc/archives/5090.html

--EOF--