table中:
ID, CONTENT
A str1@str2@str3@...
A str4@str5@...
.....
B str6@str7...
..
我想把它截取成下面格式,然后存入别的tableA str1
A str2
A str3
A str4
A str5
...
B str6
B str7
...不知道该怎样截取
ID, CONTENT
A str1@str2@str3@...
A str4@str5@...
.....
B str6@str7...
..
我想把它截取成下面格式,然后存入别的tableA str1
A str2
A str3
A str4
A str5
...
B str6
B str7
...不知道该怎样截取
解决方案 »
- ASFU版本的ORACLE是否可以使用RAC
- 表自身 自然连接 问题
- 电信难题,oracle字符集的显示!!!!!!!!
- sqlserver存储过程转oracle问题?
- 两服务器定期同步更新问题
- 半夜急求答案:ORACLE中存储过程可以是并发执行吗???
- 如何去除重复的查询结果
- 在sqlplus里执行一个update,就提示“服务器会话因致命错误而终止”,代码见内
- 请问:是否可以在oracle 中操作异种数据库吗??
- oracle表分析的存储过程是什么呀。才学习oracle!求大人!
- 麻烦各位大神帮看下 好久没用HQL了
- 视图的更新create view test as select 'ss' a from dual
SQL> with ta as(
2 select 'A' id,'str1@str2@str3' txt from dual union all
3 select 'A','str4@str5' from dual union all
4 select 'B','str6@str7' from dual)
5 , tb as(
6 select id,replace(wm_concat(txt),',','@') txt
7 from ta
8 group by id)
9 select distinct id,regexp_substr(txt,'[^@]+',1,level) txt
10 from tb
11 connect by
12 level<=length(txt)-length(replace(txt,'@',''))+1
13 order by id
14 /
ID TXT
-- --------------------------------------------------------------------------------
A str1
A str2
A str3
A str4
A str5
B str6
B str7
7 rows selected
ORA-06512: at "WMSYS.WM_CONCAT_IMPL"这是什么原因阿,字符串太长了么??
2 select 'A' id,'str1@str2@str3' txt from dual union all
3 select 'A','str4@str5' from dual union all
4 select 'B','str6@str7' from dual),
5 tb as(select rownum rn from dual connect by level<=(select max(length(txt)-length(replace(txt,'@',''))) len from ta)+1),
6 tc as(select ta.id,substr(ta.txt,instr('@'||ta.txt||'@','@',1,tb.rn),instr('@'||ta.txt||'@','@',1,tb.rn+1)-instr('@'||ta.txt||'@','@',1,tb.rn)-1) txt from ta,tb)
7 select id,txt from tc where txt is not null;
ID TXT
-- ----------------------------
A str1
A str4
B str6
A str2
A str5
B str7
A str3
7 rows selected
SQL>
类似 http://topic.csdn.net/u/20110609/14/562eade9-3759-4180-a956-a71f03b00857.html
所有相同ID字符串接在一块太长了,超出了限制最后写了一个procedure解决了