Oracle Study之--Oracle正则表达式

ゝ一世哀愁。 2022-08-05 02:43 210阅读 0赞

**Oracle Study之--Oracle正则表达式**

**案例要求：**

**建立check constraint ,要求字段输入内容必须是“00\[number\]\[number\]\[a-z\]”：**

**通过正则表达式建立如下约束（constraint）：**

17:20:13 SCOTT@ prod >desc card  
 Name                                                              Null?    Type  
 ----------------------------------------------------------------- -------- --------------------------------------------  
 ID                                                                NOT NULL VARCHAR2(10)

17:22:53 SCOTT@ prod >

17:19:05 SCOTT@ prod >alter table card drop constraint card\_ck;

Table altered.

**建立正则表达式：  
17:19:19 SCOTT@ prod >alter table card add constraint card\_ck check (REGEXP\_LIKE(id,'00\[\[:digit:\]\]\{2\}\[\[:lower:\]\]\{1\}'));**

Table altered.

Elapsed: 00:00:00.06

**测试：**

17:19:33 SCOTT@ prod >insert into card values ('0055a');

1 row created.

Elapsed: 00:00:00.01  
17:19:50 SCOTT@ prod >insert into card values ('0065z');

1 row created.

Elapsed: 00:00:00.00

17:20:04 SCOTT@ prod >insert into card values ('1188a');  
insert into card values ('1188a')  
\*  
ERROR at line 1:  
ORA-02290: check constraint (SCOTT.CARD\_CK) violated

Elapsed: 00:00:00.01

**正则表达式说明：**

表 5：REGEXP\_LIKE 操作符

表 1：定位元字符

表 2：量词或重复操作符

<table style="border-bottom:#a3a3a3 1pt solid; border-left:#a3a3a3 1pt solid; border-collapse:collapse; direction:ltr; border-top:#a3a3a3 1pt solid; border-right:#a3a3a3 1pt solid"> 
 <tbody> 
 <tr> 
 <td style="border-bottom:#a3a3a3 1pt solid; border-left:#a3a3a3 1pt solid; padding-bottom:4pt; padding-left:4pt; width:0.696in; padding-right:4pt; vertical-align:top; border-top:#a3a3a3 1pt solid; border-right:#a3a3a3 1pt solid; padding-top:4pt"> 量词 </td> 
 <td style="border-bottom:#a3a3a3 1pt solid; border-left:#a3a3a3 1pt solid; padding-bottom:4pt; padding-left:4pt; width:2.425in; padding-right:4pt; vertical-align:top; border-top:#a3a3a3 1pt solid; border-right:#a3a3a3 1pt solid; padding-top:4pt"> 说明 </td> 
 </tr> 
 <tr> 
 <td style="border-bottom:#a3a3a3 1pt solid; border-left:#a3a3a3 1pt solid; padding-bottom:4pt; padding-left:4pt; width:0.696in; padding-right:4pt; vertical-align:top; border-top:#a3a3a3 1pt solid; border-right:#a3a3a3 1pt solid; padding-top:4pt"> * </td> 
 <td style="border-bottom:#a3a3a3 1pt solid; border-left:#a3a3a3 1pt solid; padding-bottom:4pt; padding-left:4pt; width:2.425in; padding-right:4pt; vertical-align:top; border-top:#a3a3a3 1pt solid; border-right:#a3a3a3 1pt solid; padding-top:4pt"> 匹配 0 次或更多次 </td> 
 </tr> 
 <tr> 
 <td style="border-bottom:#a3a3a3 1pt solid; border-left:#a3a3a3 1pt solid; padding-bottom:4pt; padding-left:4pt; width:0.696in; padding-right:4pt; vertical-align:top; border-top:#a3a3a3 1pt solid; border-right:#a3a3a3 1pt solid; padding-top:4pt"> ? </td> 
 <td style="border-bottom:#a3a3a3 1pt solid; border-left:#a3a3a3 1pt solid; padding-bottom:4pt; padding-left:4pt; width:2.425in; padding-right:4pt; vertical-align:top; border-top:#a3a3a3 1pt solid; border-right:#a3a3a3 1pt solid; padding-top:4pt"> 匹配 0 次或 1 次 </td> 
 </tr> 
 <tr> 
 <td style="border-bottom:#a3a3a3 1pt solid; border-left:#a3a3a3 1pt solid; padding-bottom:4pt; padding-left:4pt; width:0.696in; padding-right:4pt; vertical-align:top; border-top:#a3a3a3 1pt solid; border-right:#a3a3a3 1pt solid; padding-top:4pt"> + </td> 
 <td style="border-bottom:#a3a3a3 1pt solid; border-left:#a3a3a3 1pt solid; padding-bottom:4pt; padding-left:4pt; width:2.425in; padding-right:4pt; vertical-align:top; border-top:#a3a3a3 1pt solid; border-right:#a3a3a3 1pt solid; padding-top:4pt"> 匹配 1 次或更多次 </td> 
 </tr> 
 <tr> 
 <td style="border-bottom:#a3a3a3 1pt solid; border-left:#a3a3a3 1pt solid; padding-bottom:4pt; padding-left:4pt; width:0.696in; padding-right:4pt; vertical-align:top; border-top:#a3a3a3 1pt solid; border-right:#a3a3a3 1pt solid; padding-top:4pt"> {m} </td> 
 <td style="border-bottom:#a3a3a3 1pt solid; border-left:#a3a3a3 1pt solid; padding-bottom:4pt; padding-left:4pt; width:2.425in; padding-right:4pt; vertical-align:top; border-top:#a3a3a3 1pt solid; border-right:#a3a3a3 1pt solid; padding-top:4pt"> 正好匹配&nbsp;m&nbsp;次 </td> 
 </tr> 
 <tr> 
 <td style="border-bottom:#a3a3a3 1pt solid; border-left:#a3a3a3 1pt solid; padding-bottom:4pt; padding-left:4pt; width:0.696in; padding-right:4pt; vertical-align:top; border-top:#a3a3a3 1pt solid; border-right:#a3a3a3 1pt solid; padding-top:4pt"> {m,} </td> 
 <td style="border-bottom:#a3a3a3 1pt solid; border-left:#a3a3a3 1pt solid; padding-bottom:4pt; padding-left:4pt; width:2.425in; padding-right:4pt; vertical-align:top; border-top:#a3a3a3 1pt solid; border-right:#a3a3a3 1pt solid; padding-top:4pt"> 至少匹配&nbsp;m&nbsp;次 </td> 
 </tr> 
 <tr> 
 <td style="border-bottom:#a3a3a3 1pt solid; border-left:#a3a3a3 1pt solid; padding-bottom:4pt; padding-left:4pt; width:0.696in; padding-right:4pt; vertical-align:top; border-top:#a3a3a3 1pt solid; border-right:#a3a3a3 1pt solid; padding-top:4pt"> {m, n} </td> 
 <td style="border-bottom:#a3a3a3 1pt solid; border-left:#a3a3a3 1pt solid; padding-bottom:4pt; padding-left:4pt; width:2.425in; padding-right:4pt; vertical-align:top; border-top:#a3a3a3 1pt solid; border-right:#a3a3a3 1pt solid; padding-top:4pt"> 至少匹配&nbsp;m&nbsp;次但不超过&nbsp;n&nbsp;次 </td> 
 </tr> 
 </tbody> 
</table>

表 3：预定义的 POSIX 字符类

<table style="border-bottom:#a3a3a3 1pt solid; border-left:#a3a3a3 1pt solid; border-collapse:collapse; direction:ltr; border-top:#a3a3a3 1pt solid; border-right:#a3a3a3 1pt solid"> 
 <tbody> 
 <tr> 
 <td style="border-bottom:#a3a3a3 1pt solid; border-left:#a3a3a3 1pt solid; padding-bottom:4pt; padding-left:4pt; width:0.963in; padding-right:4pt; vertical-align:top; border-top:#a3a3a3 1pt solid; border-right:#a3a3a3 1pt solid; padding-top:4pt"> 字符类 </td> 
 <td style="border-bottom:#a3a3a3 1pt solid; border-left:#a3a3a3 1pt solid; padding-bottom:4pt; padding-left:4pt; width:5.112in; padding-right:4pt; vertical-align:top; border-top:#a3a3a3 1pt solid; border-right:#a3a3a3 1pt solid; padding-top:4pt"> 说明 </td> 
 </tr> 
 <tr> 
 <td style="border-bottom:#a3a3a3 1pt solid; border-left:#a3a3a3 1pt solid; padding-bottom:4pt; padding-left:4pt; width:0.963in; padding-right:4pt; vertical-align:top; border-top:#a3a3a3 1pt solid; border-right:#a3a3a3 1pt solid; padding-top:4pt"> [:alpha:] </td> 
 <td style="border-bottom:#a3a3a3 1pt solid; border-left:#a3a3a3 1pt solid; padding-bottom:4pt; padding-left:4pt; width:5.112in; padding-right:4pt; vertical-align:top; border-top:#a3a3a3 1pt solid; border-right:#a3a3a3 1pt solid; padding-top:4pt"> 字母字符 </td> 
 </tr> 
 <tr> 
 <td style="border-bottom:#a3a3a3 1pt solid; border-left:#a3a3a3 1pt solid; padding-bottom:4pt; padding-left:4pt; width:0.963in; padding-right:4pt; vertical-align:top; border-top:#a3a3a3 1pt solid; border-right:#a3a3a3 1pt solid; padding-top:4pt"> [:lower:] </td> 
 <td style="border-bottom:#a3a3a3 1pt solid; border-left:#a3a3a3 1pt solid; padding-bottom:4pt; padding-left:4pt; width:5.112in; padding-right:4pt; vertical-align:top; border-top:#a3a3a3 1pt solid; border-right:#a3a3a3 1pt solid; padding-top:4pt"> 小写字母字符 </td> 
 </tr> 
 <tr> 
 <td style="border-bottom:#a3a3a3 1pt solid; border-left:#a3a3a3 1pt solid; padding-bottom:4pt; padding-left:4pt; width:0.963in; padding-right:4pt; vertical-align:top; border-top:#a3a3a3 1pt solid; border-right:#a3a3a3 1pt solid; padding-top:4pt"> [:upper:] </td> 
 <td style="border-bottom:#a3a3a3 1pt solid; border-left:#a3a3a3 1pt solid; padding-bottom:4pt; padding-left:4pt; width:5.112in; padding-right:4pt; vertical-align:top; border-top:#a3a3a3 1pt solid; border-right:#a3a3a3 1pt solid; padding-top:4pt"> 大写字母字符 </td> 
 </tr> 
 <tr> 
 <td style="border-bottom:#a3a3a3 1pt solid; border-left:#a3a3a3 1pt solid; padding-bottom:4pt; padding-left:4pt; width:0.963in; padding-right:4pt; vertical-align:top; border-top:#a3a3a3 1pt solid; border-right:#a3a3a3 1pt solid; padding-top:4pt"> [:digit:] </td> 
 <td style="border-bottom:#a3a3a3 1pt solid; border-left:#a3a3a3 1pt solid; padding-bottom:4pt; padding-left:4pt; width:5.112in; padding-right:4pt; vertical-align:top; border-top:#a3a3a3 1pt solid; border-right:#a3a3a3 1pt solid; padding-top:4pt"> 数字 </td> 
 </tr> 
 <tr> 
 <td style="border-bottom:#a3a3a3 1pt solid; border-left:#a3a3a3 1pt solid; padding-bottom:4pt; padding-left:4pt; width:0.963in; padding-right:4pt; vertical-align:top; border-top:#a3a3a3 1pt solid; border-right:#a3a3a3 1pt solid; padding-top:4pt"> [:alnum:] </td> 
 <td style="border-bottom:#a3a3a3 1pt solid; border-left:#a3a3a3 1pt solid; padding-bottom:4pt; padding-left:4pt; width:5.112in; padding-right:4pt; vertical-align:top; border-top:#a3a3a3 1pt solid; border-right:#a3a3a3 1pt solid; padding-top:4pt"> 字母数字字符 </td> 
 </tr> 
 <tr> 
 <td style="border-bottom:#a3a3a3 1pt solid; border-left:#a3a3a3 1pt solid; padding-bottom:4pt; padding-left:4pt; width:0.963in; padding-right:4pt; vertical-align:top; border-top:#a3a3a3 1pt solid; border-right:#a3a3a3 1pt solid; padding-top:4pt"> [:space:] </td> 
 <td style="border-bottom:#a3a3a3 1pt solid; border-left:#a3a3a3 1pt solid; padding-bottom:4pt; padding-left:4pt; width:5.112in; padding-right:4pt; vertical-align:top; border-top:#a3a3a3 1pt solid; border-right:#a3a3a3 1pt solid; padding-top:4pt"> 空白字符（禁止打印），如回车符、换行符、竖直制表符和换页符 </td> 
 </tr> 
 <tr> 
 <td style="border-bottom:#a3a3a3 1pt solid; border-left:#a3a3a3 1pt solid; padding-bottom:4pt; padding-left:4pt; width:0.963in; padding-right:4pt; vertical-align:top; border-top:#a3a3a3 1pt solid; border-right:#a3a3a3 1pt solid; padding-top:4pt"> [:punct:] </td> 
 <td style="border-bottom:#a3a3a3 1pt solid; border-left:#a3a3a3 1pt solid; padding-bottom:4pt; padding-left:4pt; width:5.112in; padding-right:4pt; vertical-align:top; border-top:#a3a3a3 1pt solid; border-right:#a3a3a3 1pt solid; padding-top:4pt"> 标点字符 </td> 
 </tr> 
 <tr> 
 <td style="border-bottom:#a3a3a3 1pt solid; border-left:#a3a3a3 1pt solid; padding-bottom:4pt; padding-left:4pt; width:0.963in; padding-right:4pt; vertical-align:top; border-top:#a3a3a3 1pt solid; border-right:#a3a3a3 1pt solid; padding-top:4pt"> [:cntrl:] </td> 
 <td style="border-bottom:#a3a3a3 1pt solid; border-left:#a3a3a3 1pt solid; padding-bottom:4pt; padding-left:4pt; width:5.112in; padding-right:4pt; vertical-align:top; border-top:#a3a3a3 1pt solid; border-right:#a3a3a3 1pt solid; padding-top:4pt"> 控制字符（禁止打印） </td> 
 </tr> 
 <tr> 
 <td style="border-bottom:#a3a3a3 1pt solid; border-left:#a3a3a3 1pt solid; padding-bottom:4pt; padding-left:4pt; width:0.963in; padding-right:4pt; vertical-align:top; border-top:#a3a3a3 1pt solid; border-right:#a3a3a3 1pt solid; padding-top:4pt"> [:print:] </td> 
 <td style="border-bottom:#a3a3a3 1pt solid; border-left:#a3a3a3 1pt solid; padding-bottom:4pt; padding-left:4pt; width:5.112in; padding-right:4pt; vertical-align:top; border-top:#a3a3a3 1pt solid; border-right:#a3a3a3 1pt solid; padding-top:4pt"> 可打印字符 </td> 
 </tr> 
 </tbody> 
</table>