OCP课程21：SQL之正则表达式

stonebox

课程目标：

使用正则表达式进行搜索，匹配及替换字符串

1、简介

正则表达式是oracle从10g开始推出的技术，Oracle SQL和PL/SQL都支持，通过函数的方式实现正则表达式的功能，使用模式串来进行搜索，修改，进行处理。

2、元字符

                               
登录/注册后可看大图

其中：

• *表示0次或者多次的发生
• |表示可选的，二选一
• ^表示一行的开始，$表示一行的结束
• []可以匹配里面的任何一个表达式
• ｛m｝表示匹配多少次
• ｛m,n｝表示至少匹配m次，不超过n次
• [::]描述某一种字符类
• \有 以下4种情况：1、表示他本身，2、引入下一个字符，3、引入一个操作符，4、什么也不做
• +匹配一次或者多次的发生
• ？匹配0次或者一次的发生
• .表示匹配任何一个字符
• ()表示分组表达式
• [==]表示恒等
• \n表示向后引用表达式
• [...]匹配里面的字符
• [^...]不匹配里面的字符
  

3、正则表达式函数

                               
登录/注册后可看大图

（1）REGEXP_LIKE

使用正则表达式进行匹配

语法：

                               
登录/注册后可看大图

例子：查找人员姓以S开头，以n结尾，中间包含teve或者tephe的人员

SQL> select first_name,last_name from employees

  2  where regexp_like(first_name,'^Ste(v|ph)en$');

FIRST_NAME           LAST_NAME

-------------------- -------------------------

Steven               King

Steven               Markle

Stephen              Stiles

（2）REGEXP_REPLACE

使用正则表达式进行匹配替换

语法：

                               
登录/注册后可看大图

例子：为国家表里面的国家名字字段的每个字母后面增加一个空格

SQL> select regexp_replace(country_name,'(.)','\1 ') "regexp_replace" from countries;

regexp_replace

--------------------------------------------------------------------------------

A r g e n t i n a

A u s t r a l i a

例子：将人员表里面的电话号码格式的第二个点改为横杠，前三位用括号括起来

SQL> SELECT phone_number,REGEXP_REPLACE(phone_number,'([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})', '(\1) \2-\3') "PHONE NUMBER" FROM employees;

PHONE_NUMBER         PHONE NUMBER

-------------------- --------------------

650.507.9833         (650) 507-9833

（3）REGEXP_INSTR

使用正则表达式返回匹配的位置

语法：

                               
登录/注册后可看大图

例子：查询位置表里面街道地址字段第一个非字母的位置大于1的街道地址以及第一个非字母的位置

SQL> select street_address,regexp_instr(street_address,'[^[:alpha:]]') loc from locations

  2  where regexp_instr(street_address,'[^[:alpha:]]')>1;

STREET_ADDRESS                                  LOC

---------------------------------------- ----------

Magdalen Centre, The Oxford Science Park          9

Schwanthalerstr. 7031                            16

Rua Frei Caneca 1360                              4

Murtenstrasse 921                                14

Pieter Breughelstraat 837                         7

Mariano Escobedo 9991                             8

6 rows selected.

例子：使用分组表达式

SQL> select regexp_instr('0123456789','(123)(4(56)(78))',1,1,0,'i',1) "Position" from dual;

  Position

----------

         2

其中：

第一个参数0123456789：为源字符串

第二个参数（123）（4（56）（78））：需要匹配的子表达式，共四个，第一个为123，第二个为45678，第三个为56，第四个为78

第三个参数1：开始搜索的位置，从第一个源字符串的第一个字符开始

第四个参数1：出现的次数，第一次出现，第一个找到的匹配项

第五个参数0：匹配字符串第一个字符的位置（如果是1，表示匹配字符串后面一个字符的位置）

第六个参数i：忽略大小写（如果是c，表示不忽略大小写）

第七个参数1：指定要匹配的是第几个子表达式，这里是第一个

（4）REGEXP_SUBSTR

使用正则表达式返回匹配的字符串

语法：

                               
登录/注册后可看大图

例子：查询位置表里面地址的街道名字

SQL> select regexp_substr(street_address,' [^ ]+ ') "Road" from locations;

Road

--------------------------------------------------------------------------------

Via

Calle

例子：查找指定字符串里面匹配的子字符串

SQL> select regexp_substr('acgctgcactgca','acg(.*)gca',1,1,'i',1) "Value" from dual;

Value

-------

ctgcact

其中：

第一个参数acgctgcactgca：为源字符串

第二个参数acg(.*)gca：需要匹配的子表达式

第三个参数1：开始搜索的位置，从第一个源字符串的第一个字符开始

第四个参数1：出现的次数，第一次出现，第一个找到的匹配项

第五个参数i：忽略大小写（如果是c，表示不忽略大小写）

第六个参数1：指定要匹配的是第几个子表达式，这里是第一个

（5）REGEXP_COUNT

使用正则表达式返回匹配的次数

语法：

                               
登录/注册后可看大图

例子：计算匹配源字符串的次数

SQL> select regexp_count('123123123123','123',2,'i') as count from dual;

     COUNT

----------

         3

其中：

第一个参数123123123123：为源字符串

第二个参数123：需要匹配的子表达式

第三个参数2：开始搜索的位置，从第一个源字符串的第二个字符开始

第四个参数i：忽略大小写（如果是c，表示不忽略大小写）

4、使用正则表达式定义约束

在定义约束的时候，可以使用正则表达式进行匹配。

例子：

SQL> create table emp8 as select email from employees;

Table created.

SQL> alter table emp8 modify(email constraint ck_email_emp8 check(regexp_like(email,'@')) novalidate);

Table altered.

novalidate表示不对现有的数据进行约束检查

SQL> insert into emp8 values('ChrisP2creme.com');

insert into emp8 values('ChrisP2creme.com')

*

ERROR at line 1:

ORA-02290: check constraint (HR.CK_EMAIL_EMP8) violated

5、相关习题：

（1）View the Exhibit and examine the description of the CUSTOMERS table. You want to add a constraint on the CUST_FIRST_NAME column of the CUSTOMERS table so that the value inserted in the column does not have numbers. Which SQL statement would you use to accomplish the task?

  A.ALTER  TABLE  CUSTOMERS  ADD  CONSTRAINT  cust_f_name
  CHECK(REGEXP_LIKE(cust_first_name,'^A-Z'))NOVALIDATE ;

  B.ALTER  TABLE  CUSTOMERS  ADD  CONSTRAINT  cust_f_name
  CHECK(REGEXP_LIKE(cust_first_name,'^[0-9]'))NOVALIDATE ;

C.ALTER  TABLE  CUSTOMERS  ADD  CONSTRAINT  cust_f_name
  CHECK(REGEXP_LIKE(cust_first_name,'[[:alpha:]]'))NOVALIDATE ;

  D.ALTER  TABLE  CUSTOMERS  ADD  CONSTRAINT  cust_f_name
  CHECK(REGEXP_LIKE(cust_first_name,'[[:digit:]]'))NOVALIDATE ;

答案：C

（2）Which three tasks can be performed using regular expression support in Oracle Database 10g?

    (Choose three.)
    A.It can be used to concatenate two strings.
    B.It can be used to find out the total length of the string.
    C.It can be used for string manipulation and searching operations.
    D.It can be used to format the output for a column or expression having string data.
    E.It can be used to find and replace operations for a column or expression having string data.

答案：CDE

（3）View the Exhibit and examine the details of the EMPLOYEES table.

Evaluate the following SQL statement:

SELECT phone_number,

REGEXP_REPLACE(phone_number,'([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})', '(\1) \2-\3')

"PHONE NUMBER"

FROM employees;

The query was written to format the PHONE_NUMBER for the employees. Which option

would be the correct format in the output?

A. xxx-xxx-xxxx

B. (xxx) xxxxxxx

C. (xxx) xxx-xxxx

D. xxx-(xxx)-xxxx

答案：C

（4）View the Exhibit and examine the data in the LOCATIONS table. Evaluate the following SQL statement:  SELECT  street_address  FROM  locations  WHERE REGEXP_INSTR(street_address,'[^[:alpha:]]') = 1；Which statement is true regarding the output of this SQL statement?

                               
登录/注册后可看大图

A.It would display all the street addresses that do not have a substring 'alpha
B.It would display all the street addresses where the first character is a special character.
C.It would display all the street addresses where the first character is a letter of the alphabet.
D.It would display all the street addresses where the first character is not a letter of the alphabet.

答案：D

（5）Evaluate the following expression using meta character for regular expression: '[^Ale|ax.r$]' ；Which two matches would be returned by this expression? (Choose two.)

A.Alex
B.Alax
C.Alxer
D.Alaxendar
E.Alexender

答案：DE

（6）View  the  Exhibit and examine  the  details  for the  CATEGORIES_TAB table.  Evaluate  the following  incomplete  SQL  statement:  SELECT category_name,category_description  FROM categories_tab； You want to display only the rows that have 'harddisks' as part of the string in the CATEGORY_DESCRIPTION column. Which two WHERE clause options can give you the desired result ？(Choose two.)

A.WHERE REGEXP_LIKE (category_description, 'hard+.s')
B.WHERE REGEXP_LIKE (category_description, '^H|hard+.s')
C.WHERE REGEXP_LIKE (category_description, '^H|hard+.s$')
D.WHERE REGEXP_LIKE (category_description, '[^H|hard+.s]')

答案：AB

（7）Given below is the list of meta character syntaxes and their descriptions in random order: Meta character syntax Description 1) ^ a) Matches character not in the list 2) [^...] b) Matches character when it occurs at the beginning of a line 3) |c) Treats the subsequent meta character as a literal 4) \ d) Matches one of the characters such as the OR operator ；Identify the option that correctly matches the meta character syntaxes with their descriptions？
A.1-b, 2-a, 3-d, 4-c
B.1-a, 2-b, 3-d, 4-c
C.1-d, 2-b, 3-a, 4-c
D.1-b, 2-c, 3-d, 2-a

答案：A

（8）View the Exhibit and examine the description of the CUSTOMERS table. You want to add a constraint on the CUST_FIRST_NAME column of the CUSTOMERS table so that the value inserted in the column does not have numbers. Which SQL statement would you use to accomplish the task？

A.ALTER  TABLE  CUSTOMERS  ADD  CONSTRAINT  cust_f_name CHECK(REGEXP_LIKE(cust_first_name,'^A-Z'))NOVALIDATE；   
B.ALTER  TABLE  CUSTOMERS  ADD  CONSTRAINT  cust_f_name CHECK(REGEXP_LIKE(cust_first_name,'^[0-9]'))NOVALIDATE；  
C.ALTER  TABLE  CUSTOMERS  ADD  CONSTRAINT  cust_f_name CHECK(REGEXP_LIKE(cust_first_name,'[[:alpha:]]'))NOVALIDATE；   
D.ALTER  TABLE  CUSTOMERS  ADD  CONSTRAINT  cust_f_name CHECK(REGEXP_LIKE(cust_first_name,'[[:digit:]]'))NOVALIDATE；

答案：C