如:
字段groupnames值为(a;b;c;d;e;f),
怎么把a,b,c,d,e,f分别写到groupname1,groupname2,groupname3,groupname4,groupname5,groupname6中,谢谢
字段groupnames值为(a;b;c;d;e;f),
怎么把a,b,c,d,e,f分别写到groupname1,groupname2,groupname3,groupname4,groupname5,groupname6中,谢谢
SELECT 'a;b;c;d;e;f' GROUPNAMES
FROM DUAL)
SELECT MAX(DECODE(LEVEL, 1, REGEXP_SUBSTR(GROUPNAMES, '[^;]+', 1, LEVEL) ) ) GROUPNAME1
,MAX(DECODE(LEVEL, 2, REGEXP_SUBSTR(GROUPNAMES, '[^;]+', 1, LEVEL) ) ) GROUPNAME2
,MAX(DECODE(LEVEL, 3, REGEXP_SUBSTR(GROUPNAMES, '[^;]+', 1, LEVEL) ) ) GROUPNAME3
,MAX(DECODE(LEVEL, 4, REGEXP_SUBSTR(GROUPNAMES, '[^;]+', 1, LEVEL) ) ) GROUPNAME4
,MAX(DECODE(LEVEL, 5, REGEXP_SUBSTR(GROUPNAMES, '[^;]+', 1, LEVEL) ) ) GROUPNAME5
,MAX(DECODE(LEVEL, 6, REGEXP_SUBSTR(GROUPNAMES, '[^;]+', 1, LEVEL) ) ) GROUPNAME6
FROM T CONNECT BY LEVEL<=LENGTH(GROUPNAMES) -LENGTH(REPLACE(GROUPNAMES, ';') ) +1;
SQL> select regexp_substr('a;b;c;d;e;f','[^;]+',1,level)
2 from dual
3 connect by
4 level<=length('a;b;c;d;e;f')-length(replace('a;b;c;d;e;f',';',''))+1
5 /
REGEXP_SUBSTR('A;B;C;D;E;F',
------------------------------
a
b
c
d
e
f
6 rows selected
--其实本意就是将字符串a;b;c;d;e;f拆分为单个单词,
--在利用level伪列来实现行转换为列
select substr(GROUPNAMES,1,instr(GROUPNAMES,';',1,1)-1) groupname1,
substr(GROUPNAMES,instr(GROUPNAMES,';',1,1)+1,instr(GROUPNAMES,';',1,2)-instr(GROUPNAMES,';',1,1)-1) groupname2,
substr(GROUPNAMES,instr(GROUPNAMES,';',1,2)+1,instr(GROUPNAMES,';',1,3)-instr(GROUPNAMES,';',1,2)-1) groupname3,
substr(GROUPNAMES,instr(GROUPNAMES,';',1,3)+1,instr(GROUPNAMES,';',1,4)-instr(GROUPNAMES,';',1,3)-1) groupname4,
substr(GROUPNAMES,instr(GROUPNAMES,';',1,4)+1,instr(GROUPNAMES,';',1,5)-instr(GROUPNAMES,';',1,4)-1) groupname5,
substr(GROUPNAMES,instr(GROUPNAMES,';',1,5)+1) groupname6 from t