Oracle字符串字段内的字符排序

这个是狙狙的sql解法。

http://blog.csdn.net/happyflystone/archive/2009/01/17/3819863.aspx

引用需求

今天和梁翁在群里聊天，小家伙突然抛出一个有意思的问题，那就是字符串字段内的字符串排序问题，比如有列 col, 有数据 'RDGS' , 要求输出为 'DGRS' 。

 

---

 

oracle分析函数支持聚合:)

create table t_test(f varchar2 ( 10 ));

insert into t_test values ( 'sdffesa' );

insert into t_test values ( 'asdflkj' );

insert into t_test values ( 'ijf92' );

 

 

select f, max ( replace (SYS_CONNECT_BY_PATH(c, ' ' ), ' ' , '' ))f1 from (

select f,rn,c,row_number()over( partition by f order by c) as ord from ( select

f,decode(rn,

1 ,substr(f, 1 , 1 ),

2 ,substr(f, 2 , 1 ),

3 ,substr(f, 3 , 1 ),

4 ,substr(f, 4 , 1 ),

5 ,substr(f, 5 , 1 ),

6 ,substr(f, 6 , 1 ),

7 ,substr(f, 7 , 1 ),

8 ,substr(f, 8 , 1 ),

9 ,substr(f, 9 , 1 ),

10 ,substr(f, 10 , 1 )

) as c,rn from

t_test a ,(

select level rn from dual connect by 1 = 1 and level <= 10 )b

where length(a.f)>=b.rn))

start with ord= 1 connect by f= prior f and ord- 1 = prior ord

group by f;

 

drop table t_test;

 

/*

F     F1

ijf92 29fij

asdflkj     adfjkls

sdffesa     adeffss

*/

 

---

 

后来受到小梁的启发，修改了一下拆分字符串的方法，可以把decode去掉

 

-- 测试环境

create table t_test(f varchar2 ( 10 ));

insert into t_test values ( 'sdffesa' );

insert into t_test values ( 'asdflkj' );

insert into t_test values ( 'ijf92' );

 

-- 测试字符串的拆分

select

f,substr(f,rn, 1 ) as c from

t_test a ,(

select level rn from dual connect by 1 = 1 and level <= 10 )b

where length(a.f)>=b.rn order by f;

 

/*

F     C

asdflkj     a

asdflkj     f

asdflkj     j

asdflkj     d

asdflkj     l

asdflkj     k

asdflkj     s

ijf92 f

ijf92 2

ijf92 i

ijf92 9

ijf92 j

sdffesa     a

sdffesa     s

sdffesa     e

sdffesa     f

sdffesa     d

sdffesa     s

sdffesa     f

*/

 

-- 测试拆分－聚合的完整语句

select f, max ( replace (SYS_CONNECT_BY_PATH(c, ' ' ), ' ' , '' ))f1 from (

select f,c,row_number()over( partition by f order by c) as ord from ( select

f,substr(f,rn, 1 ) as c from

t_test a ,(

select level rn from dual connect by 1 = 1 and level <= 10 )b

where length(a.f)>=b.rn))

start with ord= 1 connect by f= prior f and ord- 1 = prior ord

group by f;

 

drop table t_test;

 

/*

F     F1

ijf92 29fij

asdflkj     adfjkls

sdffesa     adeffss

*/