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
...不知道该怎样截取
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解决了