oracle 10g正则表达式REGEXP_LIKE用法 - 数据库编程

om gyj where regexp_like(value,'[[:punct:]]');

注意：
正则表达式只是搜索，替换，格式化等功能，格式化一般用后向引用，没有计算length和concatenate(连接串联)的

************************************************************************
enable/disable对未来的数据有约束/无约束。

validate/novalidate对已有的数据有约束/无约束。

是考字段约束的，意思是要在表CUSTOMERS的字段CUST_FIRST_NAME建个约束，使这个字段不能输入数字。

模拟答案A，以A－Z开头的，后面可以用数字，这样就不符合题意！
gyj@OCM> ALTER TABLE gyj ADD CONSTRAINT cust_f_name CHECK(REGEXP_LIKE(value,'^A-Z')) NOVALIDATE;

Table altered.

gyj@OCM> insert into gyj values(105,'A-Z12345');

1 row created.

gyj@OCM> insert into gyj values(105,'-AZ12345');
insert into gyj values(105,'-AZ12345')
*
ERROR at line 1:
ORA-02290: check constraint (GYJ.CUST_F_NAME) violated

gyj@OCM> insert into gyj values(105,'Z-A12345');
insert into gyj values(105,'Z-A12345')
*
ERROR at line 1:
ORA-02290: check constraint (GYJ.CUST_F_NAME) violated

模拟答案B：以0或9数字开头的，这样就不符合题意！
gyj@OCM> alter table gyj drop CONSTRAINT cust_f_name;

Table altered.

gyj@OCM> ALTER TABLE gyj ADD CONSTRAINT cust_f_name CHECK(REGEXP_LIKE(value,'^[09]')) NOVALIDATE;

Table altered.

gyj@OCM> insert into gyj values(105,'09g');

1 row created.

gyj@OCM>
gyj@OCM> insert into gyj values(105,'90g');

1 row created.

gyj@OCM> gyj@OCM> insert into gyj values(105,'190g');
insert into gyj values(105,'190g')
*
ERROR at line 1:
ORA-02290: check constraint (GYJ.CUST_F_NAME) violated

模拟体答案C：
gyj@OCM> alter table gyj drop CONSTRAINT cust_f_name;

Table altered.

gyj@OCM> ALTER TABLE gyj ADD CONSTRAINT cust_f_name CHECK(REGEXP_LIKE(value,'[[:alpha:]]')) NOVALIDATE;

Table altered.

gyj@OCM> insert into gyj values(105,'1');
insert into gyj values(105,'1')
*
ERROR at line 1:
ORA-02290: check constraint (GYJ.CUST_F_NAME) violated

gyj@OCM> insert into gyj values(105,'gyj');

1 row created.

模拟答案D：[[:digit:]] 任何数字，不符合题意！
gyj@OCM> alter table gyj drop CONSTRAINT cust_f_name;

Table altered.

gyj@OCM> ALTER TABLE gyj ADD CONSTRAINT cust_f_name CHECK(REGEXP_LIKE(value,'[[:digit:]]')) NOVALIDATE;

Table altered.

gyj@OCM> insert into gyj values(105,'1');

1 row created.

gyj@OCM> insert into gyj values(105,'gyj');
insert into gyj values(105,'gyj')
*
ERROR at line 1:
ORA-02290: check constraint (GYJ.CUST_F_NAME) violated

oracle 10g正则表达式REGEXP_LIKE用法(二)