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

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);把那些不符合的记录过滤掉。