PLSQL_基础系列01_正则表达REGEXP_LIKE / SUBSTR / INSTR / REPLACE(案例)

简介: 2014-11-30 Created By BaoXinjian 一、摘要 ORACLE中的支持正则表达式的函数主要有下面四个: 1 REGEXP_LIKE :与LIKE的功能相似 2 REGEXP_INSTR :与INSTR的功能相似 3 REGEXP_SUBSTR :与SUBSTR的功能相似 4 REGEXP_REPLACE :与REPLACE的功能相似 它们在用法上与Oracle SQL 函数LIKE、INSTR、SUBSTR 和REPLACE 用法相同。

2014-11-30 Created By BaoXinjian

一、摘要


ORACLE中的支持正则表达式的函数主要有下面四个:

1 REGEXP_LIKE :与LIKE的功能相似

2 REGEXP_INSTR :与INSTR的功能相似

3 REGEXP_SUBSTR :与SUBSTR的功能相似

4 REGEXP_REPLACE :与REPLACE的功能相似

它们在用法上与Oracle SQL 函数LIKE、INSTR、SUBSTR 和REPLACE 用法相同。

 

二、调用格式


1. 格式:

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

2. 参数说明:

(1). source_string:输入的字符串

(2). pattern:正则表达式

(3). position:标识从第几个字符开始正则表达式匹配。(默认为1)

(4). occurrence:标识第几个匹配组。(默认为1)

(5). return_option:0——返回第一个字符出现的位置。1:pattern下一个字符起始位置。

(6). match_parameter:取值范围

  •  i:大小写不敏感;
  •  c:大小写敏感;
  •  n:点号 . 不匹配换行符号;
  •  m:多行模式;
  •  x:扩展模式,忽略正则表达式中的空白字符。

 

2. REGEXP_LIKE / REGEXP_REPLACE / REGEXP_SUBSTR  / REGEXP_COUNT 格式



三、元字符和字符簇


1. 特殊字符

'^' 匹配输入字符串的开始位置,在方括号表达式中使用,此时它表示不接受该字符集合。

'$' 匹配输入字符串的结尾位置。如果设置了 RegExp 对象的 Multiline 属性,则 $ 也匹配 '\n' 或 '\r'。

'.' 匹配除换行符 \n之外的任何单字符。

'?' 匹配前面的子表达式零次或一次。

'*' 匹配前面的子表达式零次或多次。

'+' 匹配前面的子表达式一次或多次。

'( )' 标记一个子表达式的开始和结束位置。

'[]' 标记一个中括号表达式。

'{m,n}' 一个精确地出现次数范围,m=<出现次数<=n,'{m}'表示出现m次,'{m,}'表示至少出现m次。

'|' 指明两项之间的一个选择。例子'^([a-z]+|[0-9]+)$'表示所有小写字母或数字组合成的字符串。

\num 匹配 num,其中 num 是一个正整数。对所获取的匹配的引用。

'\' 转义符。

 

2. 字符簇

[[:alpha:]] 任何字母。

[[:digit:]] 任何数字。

[[:alnum:]] 任何字母和数字。

[[:space:]] 任何白字符。

[[:upper:]] 任何大写字母。

[[:lower:]] 任何小写字母。

[[unct:]] 任何标点符号。

[[:xdigit:]] 任何16进制的数字,相当于[0-9a-fA-F]。

 

3. 各种操作符的运算优先级

\ 转义符

(), (?, (?=), [] 圆括号和方括号

*, +, ?, {n}, {n,}, {n,m} 限定符

^, $, \anymetacharacter 位置和顺序

| “或”操作

 

四、简单案例


1. 创建测试数据

CREATE TABLE test (mc VARCHAR2 (60));

BEGIN
   INSERT INTO test VALUES   ('112233445566778899');
   INSERT INTO test VALUES   ('22113344 5566778899');
   INSERT INTO test VALUES   ('33112244 5566778899');
   INSERT INTO test VALUES   ('44112233 5566 778899');
   INSERT INTO test VALUES   ('5511 2233 4466778899');
   INSERT INTO test VALUES   ('661122334455778899');
   INSERT INTO test VALUES   ('771122334455668899');
   INSERT INTO test VALUES   ('881122334455667799');
   INSERT INTO test VALUES   ('991122334455667788');
   INSERT INTO test VALUES   ('aabbccddee');
   INSERT INTO test VALUES   ('bbaaaccddee');
   INSERT INTO test VALUES   ('ccabbddee'); 
   INSERT INTO test VALUES   ('ddaabbccee');
   INSERT INTO test VALUES   ('eeaabbccdd');
   INSERT INTO test VALUES   ('ab123');
   INSERT INTO test VALUES   ('123xy');
   INSERT INTO test VALUES   ('007ab');
   INSERT INTO test VALUES   ('abcxy');
   INSERT INTO test VALUES   ('The final test is is is how to find duplicate words.');

   COMMIT;
END;

 

2. 案例 REGEXP_LIKE

(1). {}的意思是连续几个的匹配上面的sql的意思是开头有1个到3个连续a

(2). 有连续3个a

(3). 以a开头以e结尾的字符之所以有.是因为如果写成^a*e$就变成以a开头中间都是a以e结尾的字符拉 *的意思是匹配它前面的字符

^ab*e$可否查出以a开头以e结尾的字符呢?

答案是否定的因为*虽然是可以匹配前面的b0次或者多次但是它也仅仅是匹配

以a开头中间可以有b并且都是b,或者中间没有字符,以e结尾的字符.

'^[[:alpha:]]+$'的意思是以字母从开头到结尾都包含字母的字符

(4). 小写字母或者数字开头的字符

(5). 小写字母开头的字符

(6). "^"就是一个有多种意义的字符元,主要看语意环境如果"^"是字符列中的第一个字符,就表示对这个字符串取反,因此, [^[:digit:]]就是表示查找非数字的模式即字符中不都是数字

(7). 以非数字开头的字符

 

3. 案例 REGEXP_INSTR

 

 

4. 案例 REGEXP_SUBSTR

 

5. 案例 REGEXP_REPLACE

 

五、复杂案例


1,  REGEXP_SUBSTR 为指定字符串的一部分与正则表达式建立匹配。

示例1:

SQL> select regexp_substr('The zip code 80831 is for falcon, co','[[:digit:]]{5}' ) REGEXP_SUBSTR from dual;
REGEXP_SUBSTR
------------- 80831

示例2:

SQL> select regexp_substr('The zip code 80831 is for falcon, co', '[[:alpha:]]{3,}', 1, 3) REGEXP_SUBSTR from dual;

REGEXP_SUBSTR
-------------
code

示例3

SQL> select regexp_substr('comments or questions - email feedback@plsqlbook.com', '[[:alnum:]](([_\.\-\+]?[[:alnum:]]+)*)@' 

|| '([[:alnum:]]+)(([\.-]?[[:alnum:]]+)*)\.([[:alpha:]]{2,})') REGEXP_SUBSTR from dual; REGEXP_SUBSTR ---------------------- feedback@plsqlbook.com

 
2.  REGEXP_INSTR 返回与正则表达式匹配的字符和字符串的位置。

SQL> select regexp_instr('The zip code 80831 is for falcon, co<