oracle 正则表达式拆分字符串,oracle中通过正则表达式函数处理逗号分隔的字段...

这个题目的确不大好写，其实际含义基于一个场景：

比如在oracle的某张表中，有两个字段

A1          B1

1         A,B,C,D,

2         E,F

假如现在的需求，是要统计A1中，每条记录拥有多少个数目的B1，比如

A1=1的时候，其B1的字段中有4个以逗号分隔的，所以数目为4，A1=2的，有

E,F共2个，所有数目为2

而在ORACLE中，如果使用ORACLE本身的函数，其实也是可以做到这个

效果的，核心是把这些逗号分隔的字符，逐一拆出来，变成一行行，上面的例子来说，

就要想办法变成

A1       B1

1        A

1        B

1        C

1        D

2        E

2        F

读者可能马上想到，这个其实是一对多关联关系的数据库存储方法，也是最常用的，

但有的时候，用A,B,C,D这样的方式也是可以保存一对多关系的，两种之间可以进行转换，

在oracle中有两种方法实现。

1) oracle中的正则表达式的函数REGEXP_SUBSTR

oracle中的REGEXP_SUBSTR的用法，先介绍下：

5个参数

第一个是输入的字符串

第二个是正则表达式

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

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

第五个是是取值范围：

i：大小写不敏感；

c：大小写敏感；

n：点号 . 不匹配换行符号；

m：多行模式；

x：扩展模式，忽略正则表达式中的空白字符。

全部测试数据

SQL> select * from test_reg_substr;

A

-----------------------------------

ABC123XYZ

ABC123XYZ456

Edward

检索中间的数字

SQL> SELECT

2    REGEXP_SUBSTR(a,'[0-9]+')

3  FROM

4    test_reg_substr

5  WHERE

6    REGEXP_LIKE(a, '[0-9]+');

REGEXP_SUBSTR(A,'[0-9]+')

---------------------------------

123

123

检索中间的数字(从第一个字母开始匹配，找第2个匹配项目)

SQL> SELECT

2    NVL(REGEXP_SUBSTR(a,'[0-9]+',1, 2), '-') AS a

3  FROM

4    test_reg_substr

5  WHERE

6    REGEXP_LIKE(a, '[0-9]+');

A

------------------------------------------------------

-

456

取得“字符集合”

SQL> SELECT

2    REGEXP_SUBSTR(a, '\w+')

3  FROM

4    test_reg_substr

5  WHERE

6    REGEXP_LIKE(a, '\w+');

REGEXP_SUBSTR(A,'\W+')

-------------------------------

ABC123XYZ

ABC123XYZ456

Name

取得“字符集合”(从第一个字母开始匹配，找第2个匹配项目)

SQL> SELECT

2    NVL(REGEXP_SUBSTR(a, '\w+',1, 2), '-') AS a

3  FROM

4    test_reg_substr

5  WHERE

6    REGEXP_LIKE(a, '\w+');

A

---------------------------------------------------

-

-

Edward

更多的关于这个的介绍，可以看

http://topmanopensource.iteye.com/blog/1198638

这个文的小结，小结的不错。但要注意的是，在10G以上才支持，

方法1) 使用上面的正则，语句为

Java代码

SELECT distinct A1, REGEXP_SUBSTR(B1, '[^,]+', 1, LEVEL) AS A2

FROM TEST

CONNECT BY LEVEL <= LENGTH(B1)-LENGTH(REPLACE(B1, ','))+1)

方法2)

select a1, substr(b1, 0, instr(b1, ',')) from

test union select a1, substr(b1, instr(b1, ',')+1,length(b1))

from test

方法3

使用SQL，首先在b1字段后面添加一个逗号,比如

要某条记录中B1的字段内容为'duan,shao,chu,tian'

SELECT L_COUNT,

SUBSTR('duan,shao,chu,tian' || ',',

INSTR('duan,shao,chu,tian' || ',',

',',

L_COUNT - 1,

DECODE(L_COUNT - 1, 0, 1, L_COUNT - 1)) + 1,

INSTR('duan,shao,chu,tian' || ',',

',',

1,

DECODE(L_COUNT - 1, 0, 1, L_COUNT - 1) + 1) -

INSTR('duan,shao,chu,tian' || ',',

',',

1,

DECODE(L_COUNT - 1, 0, 1, L_COUNT - 1)) - 1) AS RS

FROM DUAL,

(SELECT LEVEL L_COUNT

FROM DUAL

CONNECT BY LEVEL <

LENGTH('duan,shao,chu,tian' || ',') -

LENGTH(REPLACE('duan,shao,chu,tian' || ',', ',', '')) + 1);

其中2，3方法对oracle 9i也是可以使用的