oracle如何拆分以特定分隔的字段为多行

最近同事遇到一个问题,需要把一个字段内容按照;分隔成多行,通过度娘和查资料终于解决了这个问题,下来就来絮叨一下.

刚开始查到的都是replace、substr和instr连用

replace语法:

replace(字符串,要被替换的字符,替换后的字符)

substr语法:

substr函数格式   (俗称：字符截取函数)

格式1： substr(string string, int a, int b);

格式2：substr(string string, int a) ;

解析：

    格式1：
        1、string 需要截取的字符串
        2、a 截取字符串的开始位置（注：当a等于0或1时，都是从第一位开始截取）
        3、b 要截取的字符串的长度

    格式2：
        1、string 需要截取的字符串
        2、a 可以理解为从第a个字符开始截取后面所有的字符串。

instr语法:

instr()函数的格式  （俗称：字符查找函数）

格式一：instr( string1, string2 )    // instr(源字符串, 目标字符串)

格式二：instr( string1, string2 [, start_position [, nth_appearance ] ] )   // instr(源字符串, 目标字符串, 起始位置, 匹配序号)

解析：string2 的值要在string1中查找，是从start_position给出的数值（即：位置）开始在string1检索，检索第nth_appearance（几）次出现string2位置下标。

  注：在Oracle/PLSQL中，instr函数返回要截取的字符串在源字符串中的位置。只检索一次，也就是说从字符的开始到字符的结尾就结束。

数据就是下面这种情况:

首先看一下这三个联合函数可以实现的效果:

要想多行显示只能使用union all来实现(因为开头还有一个;,所以直接就从第二个;开始,这样是不易用的假如之后有三个字符串的时候他就失效了)

select noid,replace(substr(str,instr(str,';')+1,instr(str,';',2)-1),';') from test 
union all
select noid,replace((substr(str,instr(str,';')+1,instr(str,';',3)-1)),';') from test

 

从这个结果可以看出来,假如字段只有一个值的时候,分隔出的第二个字符串就是null,所以这种办法是行不通的;

后面又查到一个方法就是通过REGEXP_SUBSTR搭配伪列来实现

REGEXP_SUBSTR语法:

这个函数的作用是正则分隔字符串，用法为

function REGEXP_SUBSTR(String, pattern, position, occurrence, modifier)

String：需要进行正则处理的字符串

pattern：进行匹配的正则表达式

position：起始位置，从第几个字符开始正则表达式匹配（默认为1）

occurrence：标识第几个匹配组，默认为1

modifier：模式（'i'不区分大小写进行检索；'c'区分大小写进行检索。默认为'c'。）

伪列的话使用rownum或level搭配CONNECT BY来使用(它可以实现连续的值):

SELECT REGEXP_SUBSTR(';1010001830;1010001831', '[^;]+', 1, rownum)
FROM dual connect BY rownum <= LENGTH(';1010001830;1010001831') - LENGTH(regexp_replace(';1010001830;1010001831', ';', ''));

[^;]+:不包含;的任意字符，字符个数不限，但至少一个字符

向上面这样的一条数据是没问题的,但是一旦换成字段的话,他只能分隔出最长的一个字符串,剩下的都是null值(这个有点匪夷所思);

SELECT REGEXP_SUBSTR(str, '[^;]+', 1, rownum)
FROM test connect BY rownum <= LENGTH(str) - LENGTH(regexp_replace(str, ';', ''));

最后查了好多资料终于找到了正确的方法:

     select t1.noid, regexp_substr(t1.str, '[^;]+', 1, level) as hsid
    from test t1
    connect by t1.noid = prior t1.noid
    and prior dbms_random.value is not null
    and level <= REGEXP_COUNT(str, '[^;]+')

这样就是最完美的sql,下面简单看看这个sql:

REGEXP_COUNT 等价于LENGTH(str) - LENGTH(regexp_replace(str, ';', ''))

REGEXP_COUNT语法:

Oracle的11g引入此函数

  REGEXP_COUNT ( source_char, pattern [, position [, match_param]])

REGEXP_COUNT 返回pattern 在source_char 串中出现的次数。如果未找到匹配，则函数返回0。position 变量告诉Oracle 在源串的什么位置开始搜索。在开始位置之后每出现一次模式，都会使计数结果增加1。

match_param 变量支持下面几个值：

‘i’ 用于不区分大小写的匹配
‘c’ 用于区分大小写的匹配
‘n’ 允许句点(.)作为通配符去匹配换行符。如果省略该参数，则句点将不匹配换行符
‘m’ 将源串视为多行。即Oracle 将^和$分别看作源串中任意位置任何行的开始和结束，而不是仅仅看作整个源串的开始或结束。如果省略该参数，则Oracle将源串看作一行。
‘x’ 忽略空格字符。默认情况下，空格字符与自身相匹配。

connect by t1.noid = prior t1.noid
and prior dbms_random.value is not null

这个可以理解为是循环;