ORACLE按字母顺序排列字符串

这个描述起来不太好说，大家就看看下面的例子吧，其实就是把一个字符串中的字母按照字母表顺序重新排序。

SQL> select old_name,new_name
  2  from
  3  (
  4  select old_name,replace(sys_connect_by_path(c,' '),' ') new_name
  5  from
  6  (
  7  select e.ename old_name,
  8  row_number() over(partition by e.ename order by substr(e.ename,iter.pos,1))
 rn,
  9  substr(e.ename,iter.pos,1) c
 10  from emp e,
 11  (select rownum pos from emp) iter
 12  where iter.pos<=length(e.ename)
 13  order by 1
 14  )
 15  start with rn=1
 16  connect by prior rn=rn-1 and prior old_name=old_name
 17  )
 18  where length(old_name)=length(new_name);

OLD_NAME   NEW_NAME
---------- --------------------
ADAMS      AADMS
ALLEN      AELLN
BLAKE      ABEKL
CLARK      ACKLR
FORD       DFOR
JAMES      AEJMS
JONES      EJNOS
KING       GIKN
MARTIN     AIMNRT
MILLER     EILLMR
SCOTT      COSTT

OLD_NAME   NEW_NAME
---------- --------------------
SMITH      HIMST
TURNER     ENRRTU
WARD       ADRW

14 rows selected.

 

下面我们把上面那个又臭又长的查询分解了，一步步看看结果是什么，这样方便理解。当然，如果你足够聪明，也可以光看上面的，呵呵。

SQL> select e.ename old_name,
  2  row_number() over(partition by e.ename order by substr(e.ename,iter.pos,1))
 rn,
  3  substr(e.ename,iter.pos,1) c
  4  from emp e,
  5  (select rownum pos from emp) iter
  6  where iter.pos<=length(e.ename)
  7  order by 1;

OLD_NAME           RN C
---------- ---------- ---
ADAMS               1 A
ADAMS               2 A
ADAMS               3 D
ADAMS               4 M
ADAMS               5 S
ALLEN               1 A
ALLEN               2 E
ALLEN               3 L
ALLEN               4 L
ALLEN               5 N
BLAKE               1 A

OLD_NAME           RN C
---------- ---------- ---
BLAKE               2 B
BLAKE               3 E
BLAKE               4 K
BLAKE               5 L
CLARK               1 A
CLARK               2 C
CLARK               3 K
CLARK               4 L
CLARK               5 R
FORD                1 D
FORD                2 F

OLD_NAME           RN C
---------- ---------- ---
FORD                3 O
FORD                4 R
JAMES               1 A
JAMES               2 E
JAMES               3 J
JAMES               4 M
JAMES               5 S
JONES               1 E
JONES               2 J
JONES               3 N
JONES               4 O

OLD_NAME           RN C
---------- ---------- ---
JONES               5 S
KING                1 G
KING                2 I
KING                3 K
KING                4 N
MARTIN              1 A
MARTIN              2 I
MARTIN              3 M
MARTIN              4 N
MARTIN              5 R
MARTIN              6 T

OLD_NAME           RN C
---------- ---------- ---
MILLER              1 E
MILLER              2 I
MILLER              3 L
MILLER              4 L
MILLER              5 M
MILLER              6 R
SCOTT               1 C
SCOTT               2 O
SCOTT               3 S
SCOTT               4 T
SCOTT               5 T

OLD_NAME           RN C
---------- ---------- ---
SMITH               1 H
SMITH               2 I
SMITH               3 M
SMITH               4 S
SMITH               5 T
TURNER              1 E
TURNER              2 N
TURNER              3 R
TURNER              4 R
TURNER              5 T
TURNER              6 U

OLD_NAME           RN C
---------- ---------- ---
WARD                1 A
WARD                2 D
WARD                3 R
WARD                4 W

70 rows selected.

 

进一步使用sys_connect_by_path来把那些单个的字符连接起来。

 

SQL> select old_name,replace(sys_connect_by_path(c,' '),' ') new_name
  2  from
  3  (
  4  select e.ename old_name,
  5  row_number() over(partition by e.ename order by substr(e.ename,iter.pos,1))
 rn,
  6  substr(e.ename,iter.pos,1) c
  7  from emp e,
  8  (select rownum pos from emp) iter
  9  where iter.pos<=length(e.ename)
 10  order by 1
 11  )
 12  start with rn=1
 13  connect by prior rn=rn-1 and prior old_name=old_name;

OLD_NAME   NEW_NAME
---------- --------------------
ADAMS      A
ADAMS      AA
ADAMS      AAD
ADAMS      AADM
ADAMS      AADMS
ALLEN      A
ALLEN      AE
ALLEN      AEL
ALLEN      AELL
ALLEN      AELLN
BLAKE      A

OLD_NAME   NEW_NAME
---------- --------------------
BLAKE      AB
BLAKE      ABE
BLAKE      ABEK
BLAKE      ABEKL
CLARK      A
CLARK      AC
CLARK      ACK
CLARK      ACKL
CLARK      ACKLR
FORD       D
FORD       DF

OLD_NAME   NEW_NAME
---------- --------------------
FORD       DFO
FORD       DFOR
JAMES      A
JAMES      AE
JAMES      AEJ
JAMES      AEJM
JAMES      AEJMS
JONES      E
JONES      EJ
JONES      EJN
JONES      EJNO

OLD_NAME   NEW_NAME
---------- --------------------
JONES      EJNOS
KING       G
KING       GI
KING       GIK
KING       GIKN
MARTIN     A
MARTIN     AI
MARTIN     AIM
MARTIN     AIMN
MARTIN     AIMNR
MARTIN     AIMNRT

OLD_NAME   NEW_NAME
---------- --------------------
MILLER     E
MILLER     EI
MILLER     EIL
MILLER     EILL
MILLER     EILLM
MILLER     EILLMR
SCOTT      C
SCOTT      CO
SCOTT      COS
SCOTT      COST
SCOTT      COSTT

OLD_NAME   NEW_NAME
---------- --------------------
SMITH      H
SMITH      HI
SMITH      HIM
SMITH      HIMS
SMITH      HIMST
TURNER     E
TURNER     EN
TURNER     ENR
TURNER     ENRR
TURNER     ENRRT
TURNER     ENRRTU

OLD_NAME   NEW_NAME
---------- --------------------
WARD       A
WARD       AD
WARD       ADR
WARD       ADRW

70 rows selected.

 

现在只剩下最后一步需要做的了，就是再加一层select，通过条件where length(old_name)=length(new_name);把那些不符合的记录过滤掉。