oracle正则表达式

最新推荐文章于 2021-04-04 15:11:39 发布

yuetengfei

最新推荐文章于 2021-04-04 15:11:39 发布

阅读量1.1k

点赞数

分类专栏： sqlplus 文章标签： oracle 正则表达式 email sql newline insert

sqlplus 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

正则表达式有几个优点优于常见的LIKE操作符和INSTR、SUBSTR及REPLACE 函数的。这些传统的SQL 函数不便于进行模式匹配。只有LIKE 操作符通过使用%和_字符匹配，但LIKE不支持表达式的重复、复杂的更替、字符范围、字符列表和POSIX 字符类等等。

元字符（Meta Character）：

^ 使表达式定位至一行的开头

$ 使表达式定位至一行的末尾

* 匹配 0 次或更多次

? 匹配 0 次或 1 次

+ 匹配 1 次或更多次

{m} 正好匹配 m 次

{m,} 至少匹配 m 次

{m, n} 至少匹配 m 次但不超过 n 次

[:alpha:] 字母字符

[:lower:] 小写字母字符

[:upper:] 大写字母字符

[:digit:] 数字

[:alnum:] 字母数字字符

[:space:] 空白字符（禁止打印），如回车符、换行符、竖直制表符和换页符[:punct:] 标点字符

[:cntrl:] 控制字符（禁止打印）

[:print:] 可打印字符 | 分隔替换选项，通常与分组操作符 () 一起使用

( ) 将子表达式分组为一个替换单元、量词单元或后向引用单元

[char] 字符列表

Oracle 10g提供了四个regexp function: REGEXP_LIKE , REGEXP_REPLACE ,REGEXP_INSTR , REGEXP_SUBSTR 。

REGEXP_LIKE：比较一个字符串是否与正则表达式匹配

(srcstr, pattern [, match_option])

REGEXP_INSTR：在字符串中查找正则表达式，并且返回匹配的位置

(srcstr, pattern [, position [, occurrence [, return_option [, match_option]]]])

REGEXP_SUBSTR：返回与正则表达式匹配的子字符串

(srcstr, pattern [, position [, occurrence [, match_option]]])

REGEXP_REPLACE：搜索并且替换匹配的正则表达式

(srcstr, pattern [, replacestr [, position [, occurrence [, match_option]]]])

其中各参数的含义为:

srcstr: 被查找的字符数据。

pattern: 正则表达式。

occurrence: 出现的次数。默认为1。

position: 开始位置

return_option: 默认值为0，返回该模式的起始位置；值为1则返回符合匹配条件的下一个字符的起始位置。

replacestr: 用来替换匹配模式的字符串。

match_option: 匹配方式选项。缺省为c。

c：case sensitive

I：case insensitive

n：(.)匹配任何字符（包括newline)

m：字符串存在换行的时候被作为多行处理

下面通过一些具体的例子来说明如何使用这四个函数。首先创建一个测试数据表，

create table person
(
first_name varchar(20),
last_name varchar(20),
email varchar(100),
zip varchar(6)
);

insert into person values ('Steven', 'Chen', 'steven@hp.com', '123456');
insert into person values ('James', 'Li', 'jamesli@sun.com' || chr(10) || 'Lijames@oracle.com', '1b3d5f');

FIRST_NAME           LAST_NAME            EMAIL                                                                ZIP
-------------------- -------------------- ----------------------------------------------------------------- -----------
Steven               Chen                              steven@hp.com                                              123456
James                Li                                   jamesli@sun.com                                           ab23ef
                                                                  Lijames@oracle.com

REGEXP_LIKE

以数字开头

SQL> select * from person where regexp_like(zip,'^[0-9]');

FIRST_NAME LAST_NAME EMAIL ZIP
-------------------- -------------------- -------------------------------------------------------------------------------- ------
Steven Chen steven@hp.com 123456

以小写字母开头

SQL> select * from person where regexp_like(zip,'^[a-z]');

FIRST_NAME           LAST_NAME            EMAIL                            ZIP
-------------------- -------------------- ------------------------------              -----
James                Li                          jamesli@sun.com                ab23ef
                                                       Lijames@oracle.com

以a开头，f结尾

SQL> select * from person where regexp_like(zip,'^a.*f$');

FIRST_NAME           LAST_NAME            EMAIL                          ZIP
-------------------- --------------------     ------------------------------        ------
James                Li                           jamesli@sun.com              ab23ef
                                                           Lijames@oracle.com

匹配字母串

SQL> select * from person where regexp_like(first_name,'James');

FIRST_NAME           LAST_NAME            EMAIL                          ZIP
-------------------- -------------------- ------------------------------           ------
James                Li                        jamesli@sun.com                ab23ef
                                                        Lijames@oracle.com

匹配字母串(大小写不敏感)

SQL> select * from person where regexp_like(first_name,'james','i');

FIRST_NAME           LAST_NAME            EMAIL                          ZIP
-------------------- -------------------- ------------------------------            ------
James                Li                        jamesli@sun.com                ab23ef
                                                        Lijames@oracle.com

匹配包含换行符字符串

SQL> select * from person where regexp_like(email,'^james.*.com$');

FIRST_NAME LAST_NAME EMAIL ZIP
-------------------- -------------------- ------------------------------ ------

SQL> select * from person where regexp_like(email,'^james.*.com$','n');

FIRST_NAME           LAST_NAME            EMAIL                          ZIP
-------------------- -------------------- ------------------------------             ------
James                Li                          jamesli@sun.com                ab23ef
                                                         Lijames@oracle.com

匹配换行的字符串

SQL> select * from person where regexp_like(email,'^Li.*.com$');

FIRST_NAME LAST_NAME EMAIL ZIP
-------------------- -------------------- ------------------------------ ------

SQL> select * from person where regexp_like(email,'^Li.*.com$','m');

FIRST_NAME           LAST_NAME            EMAIL                          ZIP
-------------------- -------------------- ------------------------------           ------
James                Li                            jamesli@sun.com             ab23ef
                                                           Lijames@oracle.com

注意上面分别测试了不同的match_option对结果的影响。

REGEXP_INSTR

查找zip中第一个数字字符的位置

SQL> select regexp_instr(zip,'[0-9]') from person;

REGEXP_INSTR(ZIP,'[0-9]')
-------------------------
1
3

从第三个字符开始，查找zip中第二个数字字符的位置

SQL> select regexp_instr(zip,'[0-9]',3,2) from person;

REGEXP_INSTR(ZIP,'[0-9]',3,2)
-----------------------------
4
4

REGEXP_REPLACE

把zip中所有非数字字符替换为0

SQL> update person set zip=regexp_replace(zip,'[a-z]','0') where regexp_like(zip,'[a-z]');

SQL> select * from person;

FIRST_NAME           LAST_NAME            EMAIL                          ZIP
-------------------- -------------------- ------------------------------           ------
Steven               Chen                   steven@hp.com                  123456
James                Li                        jamesli@sun.com               002300
                                                        Lijames@oracle.com

在DDL中也可以正则表达式，比如Constraint, index, view

SQL> alter table person add constraint constraint_zip check (regexp_like(zip, '^[[:digit:]]+$'));

SQL> create index person_idx on person(regexp_substr(last_name, '^[[:upper:]]'));