目的:将C_EXT_ATTR2字段拆分成多行
SELECT T1.C_CODE C_ZLLXBM, T1.C_NAME C_ZLLXBMSM,T1.C_EXT_ATTR2
FROM T_CODE_BASE T1
WHERE T1.C_DMLB = '0407'--资料类型编码(T_CODE_BASE:0407)
将上面身份证分为两行,正反面
SELECT TTT.C_ZLLXBM,TTT.C_ZLLXBMSM,TTT.C_EXT_ATTR2,NVL(TTT.C_ZFMDM,TTT.C_EXT_ATTR2) C_ZFMDM
FROM (
SELECT TT.C_ZLLXBM,TT.C_ZLLXBMSM,TT.C_EXT_ATTR2, REGEXP_SUBSTR(TT.C_EXT_ATTR2, '[^,]+', 1,rownum,'i') AS C_ZFMDM
FROM (
SELECT T1.C_CODE C_ZLLXBM, T1.C_NAME C_ZLLXBMSM,T1.C_EXT_ATTR2
FROM T_CODE_BASE T1
WHERE T1.C_DMLB = '0407'--资料类型编码(T_CODE_BASE:0407)
) TT
CONNECT BY rownum <=
LENGTH(TT.C_EXT_ATTR2) - LENGTH(REGEXP_REPLACE(TT.C_EXT_ATTR2, ',', '')) + 1
) TTT
参考:
SELECT YJKEY, REGEXP_SUBSTR(ADDUSERCD, '[^,]+', 1, rownum, 'i') AS ADDUSERCD
FROM (SELECT 100000004018 AS YJKEY, '1,2,3' AS ADDUSERCD FROM DUAL) T
CONNECT BY rownum <=
LENGTH(ADDUSERCD) - LENGTH(REGEXP_REPLACE(ADDUSERCD, ',', '')) + 1;
参考资料2:https://blog.csdn.net/sofeien/article/details/80534557
最近遇到一个问题,需要把一个带有,的字符串拆分成多行。通过查询资料,这个操作需要使用以下2个关键知识:
1. REGEXP_SUBSTR函数
这个函数的作用是正则分隔字符串,用法为
function REGEXP_SUBSTR(String, pattern, position, occurrence, modifier)
__srcstr :需要进行正则处理的字符串
__pattern :进行匹配的正则表达式
__position :起始位置,从第几个字符开始正则表达式匹配(默认为1)
__occurrence :标识第几个匹配组,默认为1
__modifier :模式('i'不区分大小写进行检索;'c'区分大小写进行检索。默认为'c'。)
举几个例子说明下这个函数的用法:
select regexp_substr('1,2,3','[^,]+',1,1) result from dual;
执行结果:
select regexp_substr('1,2,3','[^,]+',1,2) result from dual;
执行结果:
可见occurrence参数用来指定要提取第几个匹配到的数据。以字符串'1,2,3'为例,这个参数分别要为1,2,3。
2.为了实现动态参数,使用 connect by
举个例子:
select rownum from dual connect by rownum<=7;
执行结果:
可见通过connect by可以构造连续的值
3.字符串中逗号的数量是不确定的,如果有2个逗号,需要提取的字段就是3个。为了确定有多少个需要提取的字段,使用regexp_replace函数
举个列子:
select regexp_replace('1,2,3',',','') from dual;
执行结果:
可见执行正则替换后,字符串中的,被删除了。通过原字符串长度和被替换后字符串长度相减,可以得到原字符串中的逗号数量,加1后得到需要提取的匹配字段数量
最终的语句为:
SELECT REGEXP_SUBSTR ('1,2,3', '[^,]+', 1,rownum)
from dual connect by rownum<=LENGTH ('1,2,3') - LENGTH (regexp_replace('1,2,3', ',', ''))+1;
执行结果: