oralce逗号分割变多行
发布时间：2020-12-12 14:01:15 所属栏目：百科 来源：网络整理
导读：方法一 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 使用函数REGEXP_SUBSTR拆分字符串： 5个参数 第
|
方法一 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 使用函数REGEXP_SUBSTR拆分字符串： 5个参数 第一个是输入的字符串 第二个是正则表达式 第三个是标识从第几个字符开始正则表达式匹配。（默认为1） 第四个是标识第几个匹配组。（默认为1） 第五个是是取值范围： i：大小写不敏感； c：大小写敏感； n：点号 . 不匹配换行符号； m：多行模式； x：扩展模式，忽略正则表达式中的空白字符。 SELECT a.*,REGEXP_SUBSTR(servicereqid,'[^;]+',l) AS servicereq FROM sum_portal_satisfaction a,51);font-family:Simsun;font-size:14px;font-style:normal;font-variant:normal;font-weight:400;letter-spacing:normal;list-style-image:none;list-style-position:outside;list-style-type:none;margin-left:0px;text-align:left;text-decoration:none;text-indent:28px;text-transform:none;word-spacing:0px;">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,51);font-family:Simsun;font-size:14px;font-style:normal;font-variant:normal;font-weight:400;letter-spacing:normal;list-style-image:none;list-style-position:outside;list-style-type:none;margin-left:0px;text-align:left;text-decoration:none;text-indent:28px;text-transform:none;word-spacing:0px;">survey_time,51);font-family:Simsun;font-size:14px;font-style:normal;font-variant:normal;font-weight:400;letter-spacing:normal;list-style-image:none;list-style-position:outside;list-style-type:none;margin-left:0px;text-align:left;text-decoration:none;text-indent:28px;text-transform:none;word-spacing:0px;">center_code,51);font-family:Simsun;font-size:14px;font-style:normal;font-variant:normal;font-weight:400;letter-spacing:normal;list-style-image:none;list-style-position:outside;list-style-type:none;margin-left:0px;text-align:left;text-decoration:none;text-indent:28px;text-transform:none;word-spacing:0px;">center_name,51);font-family:Simsun;font-size:14px;font-style:normal;font-variant:normal;font-weight:400;letter-spacing:normal;list-style-image:none;list-style-position:outside;list-style-type:none;margin-left:0px;text-align:left;text-decoration:none;text-indent:28px;text-transform:none;word-spacing:0px;">city_id,51);font-family:Simsun;font-size:14px;font-style:normal;font-variant:normal;font-weight:400;letter-spacing:normal;list-style-image:none;list-style-position:outside;list-style-type:none;margin-left:0px;text-align:left;text-decoration:none;text-indent:28px;text-transform:none;word-spacing:0px;">city_name,51);font-family:Simsun;font-size:14px;font-style:normal;font-variant:normal;font-weight:400;letter-spacing:normal;list-style-image:none;list-style-position:outside;list-style-type:none;margin-left:0px;text-align:left;text-decoration:none;text-indent:28px;text-transform:none;word-spacing:0px;">REGEXP_SUBSTR(servicereqid,l) AS servicereqid,51);font-family:Simsun;font-size:14px;font-style:normal;font-variant:normal;font-weight:400;letter-spacing:normal;list-style-image:none;list-style-position:outside;list-style-type:none;margin-left:0px;text-align:left;text-decoration:none;text-indent:28px;text-transform:none;word-spacing:0px;">REGEXP_SUBSTR(servicereqname,l) AS servicereqname,51);font-family:Simsun;font-size:14px;font-style:normal;font-variant:normal;font-weight:400;letter-spacing:normal;list-style-image:none;list-style-position:outside;list-style-type:none;margin-left:0px;text-align:left;text-decoration:none;text-indent:28px;text-transform:none;word-spacing:0px;">sum(decode(survey_value,sur_times,null)) giveup_times,--调查值 -1:未处理 0:用户放弃 1:很满意 2.满意 3.对csr不满意 4.对其它不满意 sum(sur_times) sur_times group by subslevelid,51);font-family:Simsun;font-size:14px;font-style:normal;font-variant:normal;font-weight:400;letter-spacing:normal;list-style-image:none;list-style-position:outside;list-style-type:none;margin-left:0px;text-align:left;text-decoration:none;text-indent:28px;text-transform:none;word-spacing:0px;">servicereqid,51);font-family:Simsun;font-size:14px;font-style:normal;font-variant:normal;font-weight:400;letter-spacing:normal;list-style-image:none;list-style-position:outside;list-style-type:none;margin-left:0px;text-align:left;text-decoration:none;text-indent:28px;text-transform:none;word-spacing:0px;">servicereqname,l 方法二:create table testTable ( id nvarchar2(200) primary key not null,51);font-family:Simsun;font-size:14px;font-style:normal;font-variant:normal;font-weight:400;letter-spacing:normal;list-style-image:none;list-style-position:outside;list-style-type:none;margin-left:0px;text-align:left;text-decoration:none;text-indent:28px;text-transform:none;word-spacing:0px;"> 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,51);font-family:Simsun;font-size:14px;font-style:normal;font-variant:normal;font-weight:400;letter-spacing:normal;list-style-image:none;list-style-position:outside;list-style-type:none;margin-left:0px;text-align:left;text-decoration:none;text-indent:28px;text-transform:none;word-spacing:0px;"> str_split.EXTEND; str_split (str_split.COUNT) := str; IF i >= len THEN EXIT; END IF; ELSE i := j + len1; END IF; END LOOP; RETURN str_split; END fn_split; DECLARE CURSOR c IS r c%ROWTYPE; OPEN c; FETCH c INTO r; EXIT WHEN c%NOTFOUND; DBMS_OUTPUT.put_line (r.column_value); CLOSE c; END; （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |