实战:ORACLE正则表达式_自定义函数_左右边界截取
关键字:
QQ:274906097
Longshine-DBA
时间: 2017-01-18
软件环境:ORACLE 11G
功能需求:
1、根据左右边界,从指定的字符串或字段中获取字符串。
实例1:ORACLE 的
t_txf_clob表CLOB字段中存储的HMTL内容中,获取文件路径及文件名。
<html>
<body>
<div align="left"><font face="Arial"><span style="font-size:8pt">web\EasyUI\src\service\com\easyui\service\BaseManager1.java</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:8pt">web\EasyUI\src\dao\com\easyui\dao\BaseDao2.java</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:8pt">app\AmiCommon\src\dao\com\creaway\ami\common\dao\BaseDao3.java</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:8pt">app\RmiCommu\src\dao\com\zpepc\ami\rmi\commu\dao\EasyUI\src\service\com\easyui\EasyUI\src\service\com\easyui\service\service\EasyUI\src\service\com\easyui\service\EasyUI\src\service\com\easyuieasyui\EasyUI\src\service\com\easyui\service\service\EasyUI\src\service\com\easyui\service\EasyUI\src\service\com\easyuieasyui\EasyUI\src\service\com\easyui\service\service\EasyUI\src\service\com\easyui\service\EasyUI\src\service\com\easyui\MyBatisDAOImpl4.java</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:8pt">\EasyUI\src\service\com\easyui\service\BaseDao5.java</span></font></div>
</body>
</html>
| |
如获取 “web\EasyUI\src\service\com\easyui\service\BaseManager1.java”等文件信息。
上干货:
编写自定义函数,脚本如下:
create or replace function f_substr_clob(clob_str clob,Left_str varchar2,Right_str varchar2) RETURN varchar2
/**************************************
* Name: f_substr_clob
* Author: xuefeng.Tan
* Date: 2017-01-13.
* Function: 返回字符串被指定边界字符分割后字符串。
* Parameters: clob_str: 待分割的字符串CLOB 或 varchar2。
Left_str: 左边界字符串
Right_str: 右边界字符串
* Example:
select f_substr_clob(BG_DESCRIPTION,'<span style="font-size:8pt">','</span>') from t_txf_clob
返回u_id为1和2的两行数据。
**************************************/
is
vBigStr varchar2(32676) := clob_str;
vOutStr varchar2(32676);
begin
select replace(to_char(wmsys.wm_concat(str)),',',chr(10))
into vOutStr
from (select replace(replace(regexp_substr(vBigStr,
'('||Left_str||')(.*?)('||Right_str||')',
1,
level),Left_str,''),Right_str,'') str
from dual t
CONNECT BY 1 = PRIOR 1
AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL
AND LEVEL <=
regexp_count(vBigStr,
'('||Left_str||')(.*?)('||Right_str||')' + 1) txf;
return vOutStr;
end; |
|
调用执行:
select f_substr_clob(BG_DESCRIPTION,'<span style="font-size:8pt">','</span>') from t_txf_clob ;
输出:
web\EasyUI\src\service\com\easyui\service\BaseManager1.java
web\EasyUI\src\dao\com\easyui\dao\BaseDao2.java
app\AmiCommon\src\dao\com\creaway\ami\common\dao\BaseDao3.java
app\RmiCommu\src\dao\com\zpepc\ami\rmi\commu\dao\EasyUI\src\service\com\easyui\EasyUI\src\service\com\easyui\service\service\EasyUI\src\service\com\easyui\service\EasyUI\src\service\com\easyuieasyui\EasyUI\src\service\com\easyui\service\service\EasyUI\src\service\com\easyui\service\EasyUI\src\service\com\easyuieasyui\EasyUI\src\service\com\easyui\service\service\EasyUI\src\service\com\easyui\service\EasyUI\src\service\com\easyui\MyBatisDAOImpl4.java
\EasyUI\src\service\com\easyui\service\BaseDao5.java
实例2:获取指定边界中的内容
SQL> select f_substr_clob('lsjlff: Longshine_DBA end,lsjlff: xuefengT end adljsdljel,lsjlff: 杭州文三路 end','lsjlff:',' end') from dual;
F_SUBSTR_CLOB('LSJLFF:LONGSHIN
--------------------------------------------------------------------------------
Longshine_DBA
xuefengT
杭州文三路
SQL>
欢迎各位拍砖,若对你有用请赞一个,否则
拍一个。