oralce逗号分割变多行


		oralce逗号分割变多行
 
方法一
select  a.*  , 
REGEXP_SUBSTR(a.rolecode ,'[^,]+',1,l) AS rolecode 
from p_user a,(SELECT LEVEL l FROM DUAL CONNECT BY LEVEL<=100) b
WHERE l <=LENGTH(a.rolecode) - LENGTH(REPLACE(rolecode,','))+1
   www.zzzyk.com  
使用函数REGEXP_SUBSTR拆分字符串：
 
5个参数
 
第一个是输入的字符串
 
第二个是正则表达式
 
第三个是标识从第几个字符开始正则表达式匹配。（默认为1）
 
第四个是标识第几个匹配组。（默认为1）
 
第五个是是取值范围：
  www.zzzyk.com  
i：大小写不敏感；
 
c：大小写敏感；
 
n：点号 . 不匹配换行符号；
 
m：多行模式；
 
x：扩展模式，忽略正则表达式中的空白字符。
 
SELECT a.*,REGEXP_SUBSTR(servicereqid ,'[^;]+',1,l) AS servicereq
  FROM sum_portal_satisfaction a,(SELECT LEVEL l FROM DUAL CONNECT BY LEVEL<=100) b
WHERE l <=LENGTH(servicereqid) - LENGTH(REPLACE(servicereqid,';'))+1
ORDER BY 1,2;
 
----SELECT LEVEL l FROM DUAL CONNECT BY LEVEL<=100; 生成1到100的数据行。
----l <=LENGTH(servicereqid) - LENGTH(REPLACE(servicereqid,';'))+1，注意此处是‘L’并非‘1’，上面的REGEXP_SUBSTR的第四个参数也一样。
 
 
---下面为拆分字符串，再进行的行转列
create or replace view v_sum_portal_satisfaction_sr as
select 
survey_type,
survey_time,
center_code,
center_name,
city_id,
city_name,
REGEXP_SUBSTR(servicereqid ,'[^;]+',1,l) AS servicereqid,
REGEXP_SUBSTR(servicereqname ,'[^;]+',1,l) AS servicereqname,
sum(decode(survey_value,0, sur_times,null)) giveup_times,--调查值 -1:未处理 0:用户放弃 1:很满意 2.满意 3.对csr不满意 4.对其它不满意
sum(decode(survey_value,1, sur_times,null))vsatis_times,
sum(decode(survey_value,2, sur_times,null))satis_times,
sum(decode(survey_value,3, sur_times,null))ncsr_times,
sum(decode(survey_value,4, sur_times,null))nelse_times,
sum(sur_times) sur_times
  FROM sum_portal_satisfaction a,(SELECT LEVEL l FROM DUAL CONNECT BY LEVEL<=100) b
WHERE l <=LENGTH(servicereqid) - LENGTH(REPLACE(servicereqid,';'))+1
group by
subslevelid,
center_code,
center_name,
city_id,
city_name,
survey_type,
survey_time,
servicereqid,
servicereqname,l
 
方法二:
 
create table  testTable (
       id  nvarchar2(200) primary key not null ,
       content  nvarchar2(200) not null 
)
 
insert into  testTable values ('4','馆内idx_10馆外idx_11总体idx_12');
 
 
select *  from table ( CAST (fn_split(('馆内idx_1$馆外idx_2$总体idx_3$') ,'$') as ty_str_split  )  )
 
select * from testtable b left join  table (fn_split((content), '$')   ) a   on 1=1;
 
--实现split函数
CREATE OR REPLACE TYPE ty_str_split IS TABLE OF VARCHAR2 (4000);
 
CREATE OR REPLACE FUNCTION fn_split (p_str IN VARCHAR2, p_delimiter IN VARCHAR2)
    RETURN ty_str_split
IS
    j INT := 0;
    i INT := 1;
    len INT := 0;
    len1 INT := 0;
    str VARCHAR2 (4000);
    str_split ty_str_split := ty_str_split ();
BEGIN
    len := LENGTH (p_str);
    len1 := LENGTH (p_delimiter);
 
    WHILE j < len
    LOOP
        j := INSTR (p_str, p_delimiter, i);
 
        IF j = 0
        THEN
            j := len;
            str := SUBSTR (p_str, i);
            str_split.EXTEND;
            str_split (str_split.COUNT) := str;
 
            IF i >= len
            THEN
                EXIT;
            END IF;
        ELSE
            str := SUBSTR (p_str, i, j - i);
            i := j + len1;
            str_split.EXTEND;
            str_split (str_split.COUNT) := str;
        END IF;
    END LOOP;
 
    RETURN str_split;
END fn_split;
 
DECLARE
    CURSOR c
    IS
select *  from table ( CAST (fn_split(('馆内idx_1$馆外idx_2$总体idx_3$') ,'$') as ty_str_split  )  );
 
    r c%ROWTYPE;
BEGIN
    OPEN c;
 
    LOOP
        FETCH c INTO r;
        EXIT WHEN c%NOTFOUND;
        DBMS_OUTPUT.put_line (r.column_value);
    END LOOP;
 
    CLOSE c;
END;