oracle逗号隔开行转列_oralce逗号分割变多行 Oracle中REGEXP_SUBSTR函数

本文详细介绍了Oracle中如何使用REGEXP_SUBSTR函数将逗号分隔的字符串拆分为多行。通过实例展示了如何通过此函数配合LEVEL和REPLACE方法,动态生成行数并进行行转列操作。同时提到了函数的参数用法及正则表达式模式。
摘要由CSDN通过智能技术生成

Oracle中REGEXP_SUBSTR函数 Oracle 中REGEXP_SUBSTR函数的使用说明: 题目如下:在 oracle 中,使用一条语句实现将’17,20,23’拆分成’17’,’20’,’23’的集合。 REGEXP_SUBSTR函数格式如下:function REGEXP_SUBSTR(String, pattern, position, occurrence, modifier)__srcstr     :需要进行正则处理的字符串 __pattern    :进行匹配的正则表达式 __position   :起始位置,从第几个字符开始正则表达式匹配(默认为 1)__occurrence :标识第几个匹配组,默认为 1__modifier   :模式(’i’不区分大小写进行检索;’c’区分大小写进行检索。默认为’c’。) 1、查询使用正则分割后的第一个值,也就是 17[sql] SELECT REGEXP_SUBSTR(‘17,20,23′,'[^,]+’,1,1,’i’) AS STR FROM DUAL;  结果:  www.2cto.com  STR—–17 2、查询使用正则分割后的最后一个值,也就是 23[sql] SELECT REGEXP_SUBSTR(‘17,20,23′,'[^,]+’,1,3,’i’) AS STR FROM DUAL;  结果:STR—-23 3、获取一个多个数值的列,从而能够让结果以多行的形式展示出来[sql] SELECT LEVEL FROM DUAL CONNECT BY LEVEL <=7;  结果:  www.2cto.com  LEVEL—-1234567 4、将上面 REGEXP_SUBSTR 的 occurrence 关联[sql] SELECT NVL(REGEXP_SUBSTR(‘17,20,23’, ‘[^,]+’, 1, LEVEL, ‘i’), ‘NULLL’) AS STR    FROM DUAL  CONNECT BY LEVEL <= 7;  STR  www.2cto.com  —-172023NULLNULLNULLNULL 5、优化上面的 SQL 语句,让生成的行的数量符合实际情况[sql] SELECT REGEXP_SUBSTR(‘17,20,23’, ‘[^,]+’, 1, LEVEL, ‘i’) AS STR    FROM DUAL  CONNECT BY LEVEL <=             LENGTH(‘17,20,23’) – LENGTH(REGEXP_REPLACE(‘17,20,23’, ‘,’, ”))+1;  STR—-172023

方法一

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.2cto.com

使用函数REGEXP_SUBSTR 拆分字符串:

5 个参数

第一个是输入的字符串

第二个是正则表达式

第三个是标识从第几个字符开始正则表达式匹配。(默认为 1)

第四个是标识第几个匹配组。(默认为 1)

第五个是是取值范围:

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;

露水湾 , 版权所有丨如未注明 , 均为原创丨本网站采用BY-NC-SA协议进行授权

转载请注明原文链接:oralce逗号分割变多行 Oracle中REGEXP_SUBSTR函数

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值