我有一个表AAAA 四个字段
id B C D
其中 B为clob字段（格式为逗号隔开的字符串：东方,西方,备份） c为varchar2 d为number我现在想做的事，是在AAAA 插入一条记录的时候，自动拆分成多条记录比如
1 东方,西方,备份 C D
拆分成
1 东方 C D
2 西方 C D
3 备份 C D存在另外一个表 BBB中~~ 请大家支个招~~~
id B C D
其中 B为clob字段（格式为逗号隔开的字符串：东方,西方,备份） c为varchar2 d为number我现在想做的事，是在AAAA 插入一条记录的时候，自动拆分成多条记录比如
1 东方,西方,备份 C D
拆分成
1 东方 C D
2 西方 C D
3 备份 C D存在另外一个表 BBB中~~ 请大家支个招~~~
我现在有一个表：t_test,表结构：
id name items
1 aa a,b c
2 aaa c d,e
3 bbb d,e，g我想把items中用“,”、“空格”、“全角，”隔开的字符串拆成单独的记录，用sql语句执行后想要这样的查询结果：
id name item
1 aa a
1 aa b
1 aa c
2 aaa c
2 aaa d
2 aaa e
3 bbb d
3 bbb e
3 bbb g请问我如何用sql语句来处理，谢谢！
-----------
WITH tab AS(
SELECT 1 id,'aa' NAME,'a,a,b c' items FROM dual
UNION ALL
SELECT 2,'aaa','c d,e' FROM dual
UNION ALL
SELECT 3,'bbb', 'd,e，g' FROM dual
)
SELECT id,NAME,item FROM (
SELECT DISTINCT id,NAME,item ,levelno
FROM (
SELECT id,NAME,SubStr(items,LEVEL,1) item ,LEVEL levelno
FROM tab
CONNECT BY LEVEL<=Length(items)
)
WHERE REGEXP_LIKE(item, '[[:alpha:]]' )
)
ORDER BY 1,2,3-----------------
with tb as(
select 1 id,'aa' NAME,'a,b c' items from dual
UNION ALL
select 2,'aaa','c d,e' from dual
UNION ALL
select 3,'bbb', 'd,e，g' from dual
)
select *
from (select id,name,substr(items,level,1) items
from tb connect by level<=length(items) and connect_by_root(name)=name)
where regexp_instr(items,'[[:alpha:]]+',1)>0--结果 ID NAM IT
---------- --- --
1 aa a
1 aa b
1 aa c
2 aaa c
2 aaa d
2 aaa e
3 bbb d
3 bbb e
3 bbb g已选择9行。
----------------------
select * from t_test;
ID NAME ITEMS
---------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
1 aa a,b c
2 aaa c d,e
3 bb d,e，g
SQL> with a as(select id,name,' '||f_convert(items)||' ' items from t_test),
2 b as(select rownum rn from dual connect by rownum<(select max(length(translate(items,' '||items,' ')))-1 from a))
3 select id,name,substr(items,instr(items,' ',1,rn)+1,instr(items,' ',1,rn+1)-instr(items,' ',1,rn)-1) items
4 from a,b;
ID NAME ITEMS
---------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
1 aa a
1 aa b
1 aa c
2 aaa c
2 aaa e
2 aaa d
3 bb d
3 bb g
3 bb e
9 rows selected
(
select 'a,b,c' a,1 b,2 c from dual
)
select regexp_substr(a,'[^,]+',1,rownum) a,b,c
from t1 connect by rownum<= length(regexp_replace(a,'[^,]+'))+1 a b c
--------------------------
1 a 1 2
2 b 1 2
3 c 1 2
IDX ITEM ITEMDESC
---------------------- -------------------- ----------------------------------------------------------------------------------------------------
1 aa a,b,c
2 bb c1,d1,e1
3 cc efg,fgh,ghi
--创建目标表
create table t_test_032302 (item varchar2(20),itemdesc varchar2(100));
--转换过程
BEGIN
FOR records IN
(SELECT * FROM t_test_032301
)
LOOP
INSERT INTO t_test_032302
SELECT records.item,
itemdesc
FROM
(SELECT regexp_substr(records.itemdesc,'[^,]+',1,rownum) itemdesc
FROM dual
CONNECT BY rownum<= LENGTH(regexp_replace(records.itemdesc,'[^,]+'))+1
);
END LOOP;
COMMIT;
END;
select * from t_test_032302
--结果
ITEM ITEMDESC
-------------------- ----------------------------------------------------------------------------------------------------
aa a
aa b
aa c
bb c1
bb d1
bb e1
cc efg
cc fgh
cc ghi
第一印象就是UNION ALL