Oracle常用正则表达式

这篇文章上次修改于 5 个月前，可能部分内容已经不适用，如有疑问可询问作者。

Oracle常用正则表达式

正则表达式(regular expression)：用于处理特殊的数据，特殊字符，字段分割，判断

正则表达式的4个关键字

REGEXP_LIKE(字符串/字段,查找内容) ; --模糊匹配

REGEXP_INSTR(字符串/字段,查找内容,什么位置开始查找,第几次出现); --查找位置

REGEXP_SUBSTR(字符串/字段,截取内容,起始位置,第几次出现); --截取

REGEXP_REPLACE(字符串/字段,被替换的内容,替换内容);--替换

--查找名字包含S的员工

SELECT ENAME FROM EMP

WHERE ENAME LIKE '%S%';

--用正则表达式实现：

SELECT ENAME FROM EMP

WHERE REGEXP_LIKE(ENAME,'S');

--或者

SELECT ENAME FROM EMP

WHERE REGEXP_LIKE(ENAME,'[S]');

[] --范围

[0-9] --查询字段里面是否有数字

[A-Z] --查询里面是否有字母，区分大小写的,为字符串，需要引号引起来[a-zA-Z]

+ --表示一位或者多位前面的匹配项

{} --紧挨着范围表示定义一个长度

[0-9]{11} --表示有11位数字

^ --没有紧挨范围代表含有什么

^[] --写在[]外面表示开头 --匹配字符串的开始

[^] --写在[]里面表示非

$ --表示结尾 --匹配字符串的结束

SQL
CREATE TABLE MOD_1 (VAU VARCHAR2(1000) );

INSERT INTO MOD_1 VALUES ('AHJDJI792N');
INSERT INTO MOD_1 VALUES ('17399487');
INSERT INTO MOD_1 VALUES ('AHshifhJI792N');
INSERT INTO MOD_1 VALUES ('AH281edifY98IH');
INSERT INTO MOD_1 VALUES ('AHedifYIH');
COMMIT;
SELECT * FROM MOD_1;


1)REGEXP_LIKE(字符串/字段,查找内容) 
--查找MOD_1表中包含字母的记录
SELECT * FROM MOD_1
WHERE REGEXP_LIKE(VAU,'[a-zA-Z]');

--查找MOD_1中只有字母的记录
SELECT * FROM MOD_1
WHERE REGEXP_LIKE(VAU,'^[a-zA-Z]+$');

--查找EMP表中名字为纯字母且长度为5的员工名字
SELECT ENAME FROM  EMP 
WHERE REGEXP_LIKE(ENAME,'^[a-zA-Z]{5}+$');

--查找MOD_1表中纯数字的记录
SELECT * FROM MOD_1
WHERE REGEXP_LIKE(VAU,'^[0-9]+$');

--查找MOD_1表中，第一位为A第二位为字母，第3位为数字的记录
SELECT * FROM MOD_1
WHERE REGEXP_LIKE(VAU,'^[A][a-zA-Z][0-9]');

--输入一个手机号，验证手机号的合法性
第一位为1，第二位为3579中的任意一位，纯11位数字为合法
CREATE OR REPLACE PROCEDURE SP_21(P_NUM NUMBER)
IS
BEGIN
  IF  REGEXP_LIKE(P_NUM,'^[1][3579][0-9]{9}+$')
    THEN DBMS_OUTPUT.PUT_LINE(P_NUM);
    ELSE DBMS_OUTPUT.PUT_LINE('不合法');
    END IF;
END;

BEGIN
  SP_21(11523457890);
END;
Copy

--2)查找位置 REGEXP_INSTR(字符串/字段,查找内容,什么位置开始查找,第几次出现);

--示例 查找字符'ASGAJIDHIDEAAJDIE'从第一位开始找，第二次出现A的位置

SELECT INSTR('ASGAJIDHIDEAAJDIE','A',1,2) FROM DUAL;

SELECT REGEXP_INSTR('ASGAJIDHIDEAAJDIE','A',1,2) FROM DUAL;

--示例查找字符串'ASGAJIDHIDEAAJDIE'中，第一次出现GA的位置

SELECT INSTR('ASGAJIDHIDEAAJDIE','GA') FROM DUAL; --3

SELECT REGEXP_INSTR('ASGAJIDHIDEAAJDIE','GA',1,1) FROM DUAL;

SELECT REGEXP_INSTR('ASGAJIDHIDEAAJDIE','GA') FROM DUAL;--3 后面两个参数省略，默认从一个位置开始找，找第一次出现的位置

--示例查找字符串'134ASGAJIDHIDEAAJDIE'中，第一次出现字母的位置,第二次出现数字的位置 SELECT REGEXP_INSTR('134ASGAJIDHIDEAAJDIE','[a-zA-Z]',1,1) FROM DUAL;--第一次出现字母的位置 SELECT REGEXP_INSTR('134ASGAJIDHIDEAAJDIE','[0-9]',1,2) FROM DUAL;--第二次出现数字的位置

--3)截取 --返回要截取的内容 截取的内容当做一个整体 一位进行处理 REGEXP_SUBSTR(字符串/字段,截取内容,起始位置,第几次出现); --示例 截取字符串'DHJAHDAH328DA'中AH出现的位置 substr(字符串,1,3)

SQL
SELECT SUBSTR('DHJAHDA328DA',4,2) FROM DUAL;
SELECT REGEXP_SUBSTR('DHJAHDA328DA','AH') FROM DUAL; --AH
SELECT REGEXP_SUBSTR('DHJAHDA328DA','AH',4) FROM DUAL; --AH
SELECT REGEXP_SUBSTR('DHJAHDA328DA','AH',4,2) FROM DUAL; --返回空
SELECT REGEXP_SUBSTR('DHJAHDA328DA','AH',4,3) FROM DUAL; --返回空
SELECT REGEXP_SUBSTR('DHJAHDA328DA','AH',4,1) FROM DUAL; --返回AH
SELECT REGEXP_SUBSTR('DHJAHDA328DA','AH',5) FROM DUAL;  --返回空

--示例字符串 张三-李四-王五 截取出李四
SELECT REGEXP_SUBSTR('张三-李四-王五','[^-]',1,2) FROM DUAL; --返回三
SELECT REGEXP_SUBSTR('张三-李四-王五','[^-]',1,3) FROM DUAL; --返回李
SELECT REGEXP_SUBSTR('张三-李四-王五','[^-]+',1,2) FROM DUAL;--返回李四

按照 - 进行分割
SELECT REGEXP_SUBSTR('张三-李四-王五','[^-]') FROM DUAL;--张
SELECT REGEXP_SUBSTR('张三-李四-王五','[^-]+',1,2) FROM DUAL;--张三 --不是-的多个字符串，所以把名字当做了整体

--替换 REGEXP_REPLACE()
--将dgeywo157fy中的数字替换成空格
SELECT REGEXP_REPLACE('dgeywo157fy','[0-9]') FROM DUAL;
Copy