|
oracle中通过正则表达式函数处理逗号分隔的字段
这个题目的确不大好写，其实际含义基于一个场景：
www.
比如在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本身的函数，其实也是可以做到这个
效果的，核心是把这些逗号分隔的字符，逐一拆出来，变成一行行，上面的例子来说，
就要想办法变成
www.
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
<Name>Edward</Name>
检索中间的数字
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://www./database/201210/164205.html
这个文的小结，小结的不错。但要注意的是，在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'
www.
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也是可以使用的.
方法三问题: 对第一个字符串处理异常(长度不对,并且无逗号时取值为空)
方法三改进后演示代码:
declare
s_BillList varchar2(8000); begin s_BillList:='112,23423,111,34234324,33434'; for cur in ( SELECT SUBSTR(s_BillList || ',',
INSTR(s_BillList || ',',',',L_COUNT - 1,DECODE(L_COUNT - 1, 0, 1, L_COUNT - 1)) + 1, case when L_COUNT=1 then INSTR(s_BillList || ',',',',1,DECODE(L_COUNT - 1, 0, 1, L_COUNT - 1))-1 else INSTR(s_BillList || ',',',',1,DECODE(L_COUNT - 1, 0, 1, L_COUNT - 1) + 1) - INSTR(s_BillList || ',',',',1,DECODE(L_COUNT - 1, 0, 1, L_COUNT - 1)) - 1 end) AS R_BillNo FROM DUAL,(SELECT LEVEL L_COUNT FROM DUAL CONNECT BY LEVEL <LENGTH(s_BillList || ',') -LENGTH(REPLACE(s_BillList || ',', ',', '')) + 1) ) loop dbms_output.put_line(cur.r_Billno); end loop; end; |
|
|
