表结构 cde_tabcde_id cde_idx cde_key
1 CHANGE_TYPE PP,BUY,MAG,P
2 CHANGE_TYPE MF,BUY,MAG,P现在的需求是将 cde_key的值以“,”分隔成多个字段值select cde_id,cde_idx,XX(cde_key) from cde_tab where cde_id = 1;XX()表示是否有截取字符串的方法,不要存储过程,谢谢
得到结果如下
1 CHANGE_TYPE PP BUY MAG P
1 CHANGE_TYPE PP,BUY,MAG,P
2 CHANGE_TYPE MF,BUY,MAG,P现在的需求是将 cde_key的值以“,”分隔成多个字段值select cde_id,cde_idx,XX(cde_key) from cde_tab where cde_id = 1;XX()表示是否有截取字符串的方法,不要存储过程,谢谢
得到结果如下
1 CHANGE_TYPE PP BUY MAG P
CDE_ID CDE_IDX REGEXP_REPLACE(CDE_KEY,'(,)','
---------- -------------------- --------------------------------------------------------------------------------
1 CHANGE_TYPE PP BUY MAG P
这个是4个不同字段还是
repalce(cde_key,',',' ')
---------- -------------------- --------------------------------------------------------------------------------
1 CHANGE_TYPE PP BUY MAG P
1 with t as (
2 select 1 cde_id, 'CHANGE_TYPE' cde_idx, 'PP,BUY,MAG,P' cde_key from dual
3 union all
4 select 2,'CHANGE_TYPE','MF,BUY,MAG,P' from dual
5 )
6 select cde_id,cde_idx,
7 regexp_substr(cde_key||',','([^,]+),',1,1,'i',1) f1,
8 regexp_substr(cde_key||',','([^,]+),',1,2,'i',1) f2,
9 regexp_substr(cde_key||',','([^,]+),',1,3,'i',1) f3,
10 regexp_substr(cde_key||',','([^,]+),',1,4,'i',1) f4
11* from t
SQL> / CDE_ID CDE_IDX F1 F2 F3 F4
---------- ----------- ---------- ---------- ---------- ----------
1 CHANGE_TYPE PP BUY MAG P
2 CHANGE_TYPE MF BUY MAG P
substr(cde_key,1,INSTR(cde_key,',',1,1)-1) as field1,
substr(cde_key,INSTR(cde_key,',',1,1)+1,INSTR(cde_key,',',1,2)-INSTR(cde_key,',',1,1)-1) as field2,
substr(cde_key,INSTR(cde_key,',',1,2)+1,INSTR(cde_key,',',1,3)-INSTR(cde_key,',',1,2)-1) as field3,
substr(cde_key,INSTR(cde_key,',',1,3)+1) as field4
from cde_tab
2 select 1 cde_id, 'CHANGE_TYPE' cde_idx, 'PP1,BUY12,MAG3,P2' cde_key from dual
3 union all
4 select 2,'CHANGE_TYPE','MF12,BUY134,MAG,P' from dual
5 )
6 select cde_id,cde_idx,
7 regexp_substr(cde_key||',','([^,]+),',1,1,'i',1) f1,
8 regexp_substr(cde_key||',','([^,]+),',1,2,'i',1) f2,
9 regexp_substr(cde_key||',','([^,]+),',1,3,'i',1) f3,
10 regexp_substr(cde_key||',','([^,]+),',1,4,'i',1) f4
11* from t
SQL> / CDE_ID CDE_IDX F1 F2 F3 F4
---------- ----------- ---------- ---------- ---------- ----------
1 CHANGE_TYPE PP1 BUY12 MAG3 P2
2 CHANGE_TYPE MF12 BUY134 MAG P
select 1 cde_id, 'CHANGE_TYPE' cde_idx, 'PP,BUY,MAG,P' cde_key from dual
union all
select 2,'CHANGE_TYPE','MF,BUY,MAG,P' from dual
)
select cde_id,cde_idx,
SUBSTR(cde_key,
CASE ROWNUM WHEN 1 THEN 1
ELSE INSTR(cde_key, ',', 1, ROWNUM - 1) + 1
END,
CASE ROWNUM WHEN 1 THEN INSTR(cde_key, ',', 1, ROWNUM) - 1
ELSE INSTR(cde_key, ',', 1, ROWNUM) -INSTR(cde_key, ',', 1, ROWNUM - 1) - 1
END) AS XX
from t CONNECT BY ROWNUM <= LENGTH(cde_key) - LENGTH(REPLACE(cde_key, ','))-1;这个应该可以解决了~~