oracle将某个字符串内容排序,Oracle字符串字段内的字符排序这个是狙狙的sql解法。
引⽤需求
今天和梁翁在⾥聊天，⼩家伙突然抛出⼀个有意思的问题，那就是字符串字段内的字符串排序问题，⽐如有列 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
values什么意思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 &n

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。