Oracle正则表达式

1. 基本介绍-Oracle支持正则表达式的函数

Oracle中一般常用到正则表达式的函数主要有一下5个：

1. REGEXP_LIKE：与LIKE的功能相似；
2. REGEXP_SUBSTR ：与SUBSTR的功能相似；
3. REGEXP_INSTR ：与INSTR的功能相似；
4. REGEXP_REPLACE ：与REPLACE的功能相似；
5. REGEXP_COUNT ：与COUNT的功能相似；

2. 分类介绍

2.1 Regexp_Like

Regexp_Like()函数 模糊匹配。

1. 语法规则

regexp_like(source_string,pattern[match_parameter])

2. 注释
source_string：传入的字符串，可以是常量，也可以是某个值类型为串的列；
pattern：要进行匹配的字符串；
match_parameter：文本量，进一步订制搜索，取值如下：

‘i’ 用于不区分大小写的匹配。
‘c’ 用于区分大小写的匹配。
‘n’ 允许将句点“.”作为通配符来匹配换行符。如果省略该参数，句点将不匹配换行符。
‘m’ 将源串视为多行。即将“^”和“$”分别看做源串中任意位置任意行的开始和结束，而不是看作整个源串的开始或结束。如果省略该参数，源串将被看作一行来处理。
如果取值不属于上述中的某个，将会报错。如果指定了多个互相矛盾的值，将使用最后一个值。如’ic’会被当做’c’处理。
省略该参数时：默认区分大小写、句点不匹配换行符、源串被看作一行。

3.应用示例

select * from users where regexp_like('MY INFO: Anxpp,23，and boy','[[:digit:]]');--23
    
select * from hx_dj.dj_nsrxx where regexp_like(nsrmc,'[[:digit:]]');--结果得出nsrmc里有数字的记录

2.2 Regexp_Substr

Regexp_Substr()函数指定返回串的起点和终点 (某段字符串)。

1. 语法规则

regexp_substr(source_string,pattern[,position[,occurrence[,match_parameter]]])

2. 注释
source_string：传入的字符串，可以是常量，也可以是某个值类型为串的列；
pattern：要进行匹配的字符串；
position：从字符串开始搜索的位置。默认为1；
occurrence：指在字符串中第几次出现。默认值1；
match_parameter：文本量，进一步订制搜索，取值如下：

‘i’ 用于不区分大小写的匹配。
‘c’ 用于区分大小写的匹配。
‘n’ 允许将句点“.”作为通配符来匹配换行符。如果省略该参数，句点将不匹配换行符。
‘m’ 将源串视为多行。
‘m’的其他规则同Regexp_Like();

3.应用示例

select regexp_substr('MY INFO: Anxpp,22，and boy','my',1,1,'i') from users;--MY

2.3 Regexp_Instr

Regexp_Instr()函数返回搜索模式的起点和终点（整数）。如果没有发现匹配的值，将返回0。

1. 语法规则

regexp_instr(source_string,pattern[,position[,occurrence[,return_option[,match_parameter]]]])

2. 注释
return_option：为0时，返回第一个字符出现的位置，与instr作用相同。为1时，返回所搜索字符出现以后下一个字符的位置。默认为0

• Instr()函数：
  Instr(char1,char2[,n[,m]])
  获取子串char2在字符串char1中的位置。n为其实搜索位置，m为子串出现的次数；n为负，则从尾部开始搜索；n\m默认为1；
  select INSTR(‘JellyThink’, ‘Jelly’, 1) from dual; – 1
  select INSTR(‘中移建设有限公司’,‘公’,1,0) from dual;–0

3.应用示例

select regexp_instr('MY INFO: Anxpp,23，and boy','[[:digit:]]') from users;--16

2.4 Regexp_Replace

Regexp_Replace()函数,replace的增强版。

• Replace()函数
  Replace(char,search_str[,replacement_str])
  将字符串char中的子串search_str替换成replacement_str；如果search_str=null，返回char；如果replacement_str=null，则会去掉char中的search_str；
  select REPLACE(‘jellythink’, ‘think’, ’ is good’) from dual; – jelly is good

1. 语法规则

regexp_replace(source_string,pattern[,replace_string[,position[,occurrence[,match_parameter]]]])

2. 注释
replace_string：表示用什么来替换source_string中与pattern匹配的部分。

occurrence：为非负整数，0表示所有匹配项都被替换，为正数时替换第n次匹配。

3.应用示例

select regexp_replace('电话：023  5868-8888 邮箱：anxppp@163.com',
        '.*([[:digit:]]{3})([^[:digit:]]{0,2})([[:digit:]]{4})([^[:digit:]]{0,2})([[:digit:]]{4}).*',
        '(\1)\3\5'
    ) phone from users;--(023)58688888

2.5 Regexp_Count

Regexp_Count()函数返回在源串中出现的模式的次数。

1. 语法规则

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

2. 注释
REGEXP_COUNT返回pattern在source_char串中出现的次数。如果未找到匹配，函数返回0；

3.应用示例

select regexp_count('MY INFO: Anxpp,23，and boy','an',1,'i') from users;--2

3. Oracle中正则表达式相关的运算符

4. 实际应用

1. 查找id为4位数的记录

select data_object_id,object_name from HH where regexp_like(data_object_id,'^[[:digit:]]{3}$');
select data_object_id,object_name from HH where regexp_like(data_object_id,'^\d{3}$');

2. 查找对象名包含英文和下划线的记录

select object_name from HH where regexp_like(object_name,'^[a-z|A-Z|_]*$');

3. 查找对象名为全英文，并且以N结尾`

select object_name from HH where regexp_like(object_name,'^[a-z|A-Z]*N$');

4. 查找以非数字开头的员工信息

select object_name from HH where regexp_like(object_name,'^[^\d]');

5. 从第三个字符开始，查找员工编号中第二个非数字字符的位置

select regexp_instr(object_name,'[^[:alnum:]]',3,2) from HH;

6. 返回从第二个字符开始检索，并且对象名以L开头$结尾的子串

select object_name,regexp_substr(object_name,'L.*\$$',2) from HH;

7. 把对象名中所有非字母字符替换为“A”

select regexp_replace(object_name,'[^a-z|A-Z]','A') from HH;

8. 查询字符串中was的出现次数，忽略大小写

select regexp_count ('THE PRO-NIECE WAS BORN TODAY, SO EXCITING!', 'Was', 1,'i') from dual;

9. 匹配身份证号码，不能有中文或点符号等

--事前准备：建立测试数据
CREATE TABLE T_ZDW_ZZBDS_19081301 AS 
WITH V AS(
SELECT '44052519510409032X' FDDBRSFZJHM FROM DUAL UNION ALL--正确
SELECT '44052519510409中文2X' FDDBRSFZJHM FROM DUAL UNION ALL--带中文的情况
SELECT '4405251951040903.X' FDDBRSFZJHM FROM DUAL UNION ALL--带其他字符的情况
SELECT '445281199103272170' FDDBRSFZJHM FROM DUAL UNION ALL--正确
SELECT '44528119910327X' FDDBRSFZJHM FROM DUAL UNION ALL--正确（15位数）
SELECT '44528119910327X170' FDDBRSFZJHM FROM DUAL UNION ALL--X不是最后一位数的情况
SELECT '44052519510409032x' FDDBRSFZJHM FROM DUAL UNION ALL--小写x情况，正常来说是不对的，可能手动输入并且系统上没校验
SELECT '4452811991032' FDDBRSFZJHM FROM DUAL UNION ALL--位数不够15位的情况
--SELECT '44052519510409032X' FDDBRSFZJHM FROM DUAL UNION ALL--最后一个X为全角输入的情况
SELECT '44052519510409032' FDDBRSFZJHM FROM DUAL --位数不够18位的情况
)
SELECT * FROM V;

--身份证的X只可能在最后一位数上，要么存在要么是数字1或0
--（原因是身份证号码的组成有它对应的计算公式，其计算结果用case when分类只有0,1，和X，感兴趣可以百度。）
--并且身份证号码是数字和X的组合 ，分为15位和18位。
SELECT *
  FROM T_ZDW_ZZBDS_19081301
 WHERE REGEXP_LIKE(FDDBRSFZJHM, '^[[:digit:]]{17}[0-9X]$')
    OR REGEXP_LIKE(FDDBRSFZJHM, '^[[:digit:]]{14}[0-9X]$');
--结果显示：
44052519510409032X
445281199103272170
44528119910327X

修改记录

时间	内容
2019年8月09日	第一次发布