浅析正则表达式在Oracle中的简单使用

SQL干货分享 2021-04-19

973

（CSDN博主：写代码也要符合基本法）

正道的光

各位看官老爷大家早上好，我们今天总结一下正则表达式在 PL/SQL 中是怎么使用的

正则表达式 Regular Expression 又称规则表达式，是对字符串操作的一种逻辑公式，就是用事先定义好的一些特定字符、及这些特定字符的组合，来表达对字符串的一种过滤逻辑

正则表达式有多好用，懂的都懂

此时假设我们大家已经都充分了解了正则表达式的语法，接下来我们一起看看在 PL/SQL 中，尤其是 LIKE、REPLACE 这些字符串操作函数力(shen)不(tou)从(zhi)心(le)的时候，如何使用正则表达式解决问题

PL/SQL 中与正则表达式相关的运算函数主要有以下五个

REGEXP_LIKE：返回指定字符串是否符合给定的正则表达式
REGEXP_SUBSTR：返回在指定字符串中截取出的符合给定正则表达式的子串
REGEXP_INSTR：返回在指定字符串中符合给定正则表达式的子串的开始位置
REGEXP_REPLACE：返回将指定字符串中符合给定正则表达式的子串替换为指定子串后的结果
REGEXP_COUNT：返回字符串中包含符合给定正则表达式的子串的个数

也就是说它们都具备类似去掉前缀后对应函数的功能

假如有一天，客户需要我们开发一个功能，要求系统验证用户输入的邮箱地址是否是格式规范的

这时如果我们使用 LIKE 来进行检验，等写好了头发应该都掉光了

而使用正则表达式 + REGEXP_LIKE 就能轻松解决

SELECT 1
  FROM dual
 WHERE regexp_like('yusuf@besthelp.vip.com'
                  ,'^([a-z0-9_\.-]+)@([\da-z\.-]+)\.([a-z\.]{2,6})$');

REGEXP_LIKE(String, Regexp)

String：指定字符串或字段内容

Regexp：用以匹配的正则表达式

看着有点别扭，其实和 LIKE 一样，只能在 WHERE 条件里面使用，只不过相当于把 LIKE 前后的内容都放到了参数里面

现在又要假如了，假如在一个项目中，我们需要处理外围系统通过接口传入的字符串，由于种种原因，这个字符串不是 JSON 或者其它交换格式的，而是将各种信息按照固定的顺序用特别分隔符拼起来的

比如，张三|白嫖|违反治安管理

如果使用 SUBSTR 和 INSTR 组合，程序员小刘就要脸上笑嘻嘻地写下形如这样的语句

SQL> WITH str AS
  2   (SELECT '张三|白嫖|违反治安管理' s, '|' sp FROM dual)
  3  SELECT substr(s, 1, instr(s, sp) - 1) name
  4        ,substr(s
  5               ,instr(s, sp) + 1
  6               ,instr(s, sp, instr(s, sp) + 1) - instr(s, sp) - 1) incident
  7        ,substr(s, instr(s, sp, instr(s, sp) + 1) + 1) charge
  8    FROM str;


NAME   INCIDENT CHARGE
------ -------- ------------------
张三   白嫖     违反治安管理

刚写完代码，外围系统的工作人员发来 QQ：“不好意思啊，哥，刚才的示例写错了，还有年龄和性别，麻烦改一下”

张三|男|33|白嫖|违反治安管理

小刘的脸上写满了开心，当时就写下了两千字的辞职信

上面的例子可读性极差，而且极难维护，一不小心就会写错

REGEXP_SUBSTR 最常应用的场景其实就是拆分字符串了

SQL> WITH str AS
  2   (SELECT '张三|男|33|白嫖|违反治安管理' s FROM dual)
  3  SELECT regexp_substr(s, '[^\|]+', 1,level) sub
  4    FROM str
  5  CONNECT BY LEVEL <= regexp_count(s, '\|') + 1;


SUB
----------------
张三
男
33
白嫖
违反治安管理

妈妈再也不用担心小刘会辞职了！

REGEXP_SUBSTR(String, Regexp, StartPos, Occurence, Mode)

REGEXP_COUNT(String, Regexp, StartPos, Mode)

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

Ocurrence：指定第几个匹配子串，默认为1

Mode：'i'-不区分大小写检索，'c'-区分大小写检索，'n'-点号(.)不匹配换行符，'m'-多行模式，'x'-忽略正则表达式中的空白字符，默认为'c'

完成了上面的任务，小刘刚泡了一杯茶，友好的外围系统又开发了一个接口，传来一个字符串，拼接了一系列事件的结果，比如 S 代表完全成功，P 代表部分成功，X 代表可能成功，F 代表完全失败

而小刘的工作是找出第一个非失败标志出现的位置

小刘捡起几根头发贴在脑袋上，开始了编码

SQL> WITH str AS
  2   (SELECT 'FFFFPXMMS' s FROM dual)
  3  SELECT least(instr(s, 'S'), instr(s, 'P'), instr(s, 'X')) FROM str;


LEAST(INSTR(S,'S'),INSTR(S,'P'),INSTR(S,'X'))
---------------------------------------------
                                            5

按照礼节，这时对方开发又发来 QQ：“哥...”

小刘退了 QQ，捡起了垃圾桶里皱巴巴的辞职信

其实小刘就是吃了没文化的亏，REGEXP_INSTR 是做这种事情的最佳选择

SQL> WITH str AS
  2   (SELECT 'FFFFPXMMS' s FROM dual)
  3  SELECT regexp_instr(s, 'S|P|X') FROM str;


REGEXP_INSTR(S,'S|P|X')
-----------------------
                      5

REGEXP_INSTR(String, Regexp, StartPos, Occurence, ReturnOpt, Mode)

ReturnOpt：0-返回出现的开始位置，1-返回字符发生之后的位置（结束位置+1），默认为0

小刘：还剩最后一个函数，直接说吧，别折腾我了

实际业务场景中，有时会遇到一些“灵异”事件：在 EBS 某个表单中，填入自己需要的信息，保存时却被告知 FRM-40654 记录已被另一个用户更新

开发查来查去，最后发现问题还真是源自凡胎肉眼看不见的力量！

用户在录入某些业务数据时，实际的操作可能是从其它文件复制粘贴来的，难免会把诸如空格、制表符、回车这类无形的字符复制进来，问题在于字符串首尾处的空白字符会造成表单中的“假锁”现象，也就是上面所说的问题。

这时需要我们在用户录入数据后、写入数据表前，加以处理，将字符串首尾处的空白字符去掉，使用 TRIM 操作的话，需要考虑各种空白字符的可能，这时使用 REGEXP_REPLACE 则可轻松解决

SQL> SELECT regexp_replace(chr(9) || chr(10) || chr(13) || chr(32) || '?' ||
  2                        chr(9) || chr(10) || chr(13) || chr(32)
  3                       ,'(^\s*)|(\s*$)'
  4                       ,'') RESULT
  5    FROM dual;


RESULT
------
?

REGEXP_REPLACE(String, Regexp, ReplaceStr, StartPos, Occurence, Mode)

ReplaceStr：替换子串，默认为NULL

聪明的你，学废了吗？

oracle

文章转载自SQL干货分享，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

浅析正则表达式在Oracle中的简单使用

评论