OCP課程21：SQL之正規表示式

課程目標：

使用正規表示式進行搜尋，匹配及替換字串

 

 

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