常规字符拆分方式
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
示例:计算一个二进制字符串中连续为0的最大个数
select max(length(REGEXP_SUBSTR(str, '[^1]+', 1, LEVEL))) STR from (select '100010100000000' str from dual) CONNECT BY LEVEL <= REGEXP_COUNT(str, '[^1]+');