首页 > 代码库 > oracle利用正则表达式对字符串进行拆分

oracle利用正则表达式对字符串进行拆分

常规字符拆分方式

select REGEXP_SUBSTR(str, [^,]+, 1, LEVEL) STR
  from (select 11,12,13,14,15,16 str from dual)
CONNECT BY LEVEL <= REGEXP_COUNT(str, [^,]+);

 

使用特数字符拆分:

oracle文本中的换行符、回车符、制表符
制表符 chr(9)
换行符 chr(10)
回车符 chr(13)

换行符拆分示例:

with data_change as
(select REGEXP_SUBSTR(str, [^ || chr(10) || ]+, 1, LEVEL) STR
from (select A
B
C
D
E str
from dual)
CONNECT BY LEVEL <= REGEXP_COUNT(str, [^ || chr(10) || ]+))
select c.str from data_change c

 

oracle利用正则表达式对字符串进行拆分