如何拆分字符串

chenyonge 2011-01-26 09:55:42
有一列数据如下
,1,1,5,7,3,2,2,1,
如何把这列数据按数字顺序排序,并去掉重复数字,变为如下效果
,1,2,3,5,7,

请高手指点 谢谢
...全文
106 10 打赏 收藏 转发到动态 举报
写回复
用AI写文章
10 条回复
切换为时间正序
请发表友善的回复…
发表回复
lxyzxq2008 2011-01-27
  • 打赏
  • 举报
回复

--修改下排序和逗号问题
with tab as
(
select ',1,1,5,7,3,2,2,1,' id from dual
)
select ',' || wm_concat(id) || ',' id from (
select distinct replace(id,',','') id from
(select substr(id,level,2) id from tab connect by level<=length(id))
where replace(id,',','') is not null
order by to_number(replace(id,',',''))
)
---------------------------------------
ID
-----------
,1,2,3,5,7,
lxyzxq2008 2011-01-27
  • 打赏
  • 举报
回复

with tab as
(
select ',1,1,5,7,3,2,2,1,' id from dual
)
select wm_concat(distinct replace(id,',','')) from
(select substr(id,level,2) id from tab connect by level<=length(id))
where id > '0' order by replace(id,',','')
--------------------------
wm_concat(distinct replace(id,',',''))
---------
1,2,3,5,7
UPC子夜 2011-01-27
  • 打赏
  • 举报
回复
with tab as (select '1,1,3,7,3,2,2,1,' as chr from dual)
select replace(','||t.num0||','||t.num1||','||t.num2||','||t.num3||','||t.num4||','||t.num5||','||t.num6||','||t.num7||','||t.num8||','||t.num9,',-1','')as result from (select
case when instr((select chr from tab),'0',1,1)>0 then '0' else '-1' end as num0,
case when instr((select chr from tab),'1',1,1)>0 then '1' else '-1' end as num1,
case when instr((select chr from tab),'2',1,1)>0 then '2' else '-1' end as num2,
case when instr((select chr from tab),'3',1,1)>0 then '3' else '-1' end as num3,
case when instr((select chr from tab),'4',1,1)>0 then '4' else '-1' end as num4,
case when instr((select chr from tab),'5',1,1)>0 then '5' else '-1' end as num5,
case when instr((select chr from tab),'6',1,1)>0 then '6' else '-1' end as num6,
case when instr((select chr from tab),'7',1,1)>0 then '7' else '-1' end as num7,
case when instr((select chr from tab),'8',1,1)>0 then '8' else '-1' end as num8,
case when instr((select chr from tab),'9',1,1)>0 then '9' else '-1' end as num9 from dual )t
coolkisses 2011-01-27
  • 打赏
  • 举报
回复
上面的语法虽然可以解决问题,但只是针对一个字段、一条记录。如果再多一条记录,这个语句的结果就不能用了。
必须把 rownum 改成 其它名词,如 level,请试一下。
心中的彩虹 2011-01-27
  • 打赏
  • 举报
回复

with tb as(
select 'abc' str, ',1,1,5,7,3,2,2,1,' id from dual
union all
select 'edf' str, ',11,12,15,17,13,12,12,11,' id from dual
)
select str,','||max(id)||','
from (select str,wm_concat(id) over(partition by str order by id) id
from (select distinct str,substr(id,instr(id,',',1,level)+1,instr(id,',',1,level+1)-instr(id,',',1,level)-1) id
from tb
connect by level<length(id)-length(replace(id,',','')) order by str,id))
group by str
---结果
abc ,1,2,3,5,7,
edf ,11,12,13,15,17,
Oraclefans_ 2011-01-27
  • 打赏
  • 举报
回复


SQL>
SQL> with tb as
2 (
3 select ',1,1,5,7,3,2,2,1,' id from dual
4 )
5 select wm_concat(rs)
6 from (select distinct regexp_substr(id, '[^,]', 1, rownum) rs
7 from tb
8 connect by rownum <= length(regexp_replace(id, '\d', ''))
9 order by rs)
10 ;

WM_CONCAT(RS)
--------------------------------------------------------------------------------
1,2,3,5,7

SQL>
  • 打赏
  • 举报
回复
[Quote=引用 3 楼 chenyonge 的回复:]
我说的是 ,1,1,5,7,3,2,2,1, 在一个数据单元格中,不是多行
[/Quote]

已写入 file afiedt.buf

1 with tb as(
2 select ',1,1,5,7,3,2,2,1,' id from dual
3 )
4 select wm_concat(newid)
5 from(select distinct
6 substr(id,instr(id,',',1,rownum)+1,
7 instr(id,',',1,rownum+1)-instr(id,',',1,rownum)-1) newid
8 from tb
9 connect by rownum <= length(id)-length(replace(id, ',', ''))-1
10* order by newid)
scott@YPCOST> /

WM_CONCAT(NEWID)
---------------------------------------------------------------------
1,2,3,5,7
chenyonge 2011-01-26
  • 打赏
  • 举报
回复
我说的是 ,1,1,5,7,3,2,2,1, 在一个数据单元格中,不是多行

gelyon 2011-01-26
  • 打赏
  • 举报
回复

--没测试,试试:

with tab as(
select ',1,1,5,7,3,2,2,1,' id from dual
)
select ','||wm_concat(newid)over(order by newid)||',' from(
select distinct substr(id,instr(id,',',1,level)+1,
instr(id|,',',1,level+1)-instr(id,',',1,level)-1) newid
from tab
connect by
level <= length(id) - length(replace(id,',',''))-1
)
  • 打赏
  • 举报
回复
[Quote=引用楼主 chenyonge 的回复:]
有一列数据如下
,1,1,5,7,3,2,2,1,
如何把这列数据按数字顺序排序,并去掉重复数字,变为如下效果
,1,2,3,5,7,

请高手指点 谢谢
[/Quote]

WITH tb AS(
select 1 flag from dual union all
select 1 from dual union all
select 5 from dual union all
select 7 from dual union all
select 3 from dual union all
select 2 from dual union all
select 2 from dual union all
select 1 from dual
)
select wm_concat(flag)
from (
select distinct flag from tb order by flag)

WM_CONCAT(FLAG)
-------------------
1,2,3,5,7

3,492

社区成员

发帖
与我相关
我的任务
社区描述
Oracle 高级技术相关讨论专区
社区管理员
  • 高级技术社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧