各位达人,小弟想实现一个字段,只能由字母打头(大小写均可),后面是7位阿拉伯数字,且最后一位不能为0我是这么实现的:
SQL> alter table d add(name varchar2(8)
2 constraint ck_1 check(name like '[a-z,A-Z][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'))
3 ;表已更改。做好以后 ,发现有问题,在插入数据时报违反约束
SQL> insert into d(name) values('A0000001');
insert into d(name) values('A0000001')
*
第 1 行出现错误:
ORA-02290: 违反检查约束条件 (SCOTT.CK_1)请教各位,我这里出了什么问题?或者是这样实现是错的,还是没有指定哪一位是首字母呢?还是用正则表达式,如果用正则表达式应该如何编写,谢谢!在线跪求~~~
SQL> alter table d add(name varchar2(8)
2 constraint ck_1 check(name like '[a-z,A-Z][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'))
3 ;表已更改。做好以后 ,发现有问题,在插入数据时报违反约束
SQL> insert into d(name) values('A0000001');
insert into d(name) values('A0000001')
*
第 1 行出现错误:
ORA-02290: 违反检查约束条件 (SCOTT.CK_1)请教各位,我这里出了什么问题?或者是这样实现是错的,还是没有指定哪一位是首字母呢?还是用正则表达式,如果用正则表达式应该如何编写,谢谢!在线跪求~~~
add constraint CK_1
check (upper(SUBSTRB(NAME,1,1))>='A' AND upper(SUBSTRB(NAME,1,1))<='Z' AND TO_NUMBER(SUBSTRB(NAME,2,7))>0);
create table t(
id number(18,0),
name varchar2(8),
constraint ck_1 check((substr(name,1,1)>='A') AND (substr(name,1,1)<='z')),
constraint ck_2 check((substr(name,2,1)>='0') AND (substr(name,2,1)<='9')),
constraint ck_3 check((substr(name,3,1)>='0') AND (substr(name,3,1)<='9')),
constraint ck_4 check((substr(name,4,1)>='0') AND (substr(name,4,1)<='9')),
constraint ck_5 check((substr(name,5,1)>='0') AND (substr(name,5,1)<='9')),
constraint ck_6 check((substr(name,6,1)>='0') AND (substr(name,6,1)<='9')),
constraint ck_7 check((substr(name,7,1)>='0') AND (substr(name,7,1)<='9')),
constraint ck_8 check((substr(name,8,1)>='1') AND (substr(name,7,1)<='9')),
constraint ck_9 check(length(name)=8)
);-- 最后一位必须为1,插入失败
insert into t(id,name) values(1,'a1111110');-- 插入成功
insert into t(id,name) values(1,'a1111111');-- 第一位为非字母,插入失败
insert into t(id,name) values(1,'01111111');-- 第一位为非字母,插入失败
insert into t(id,name) values(1,'01111111');
drop table t purge;
create table t(
id number(18,0),
name varchar2(8),
constraint ck_1 check((substr(name,1,1)>='A') AND (substr(name,1,1)<='z'))
constraint ck_2 check((to_number(reverse(substr(name,2,7)))>=1000000) and (to_number(reverse(substr(name,2,7)))<=9999999))
);
drop table t purge;
create table t(
id number(18,0),
name varchar2(8),
constraint ck_1 check((substr(name,1,1)>='A') AND (substr(name,1,1)<='z')),
constraint ck_2 check((to_number(reverse(substr(name,2,7)))>=1000000) and (to_number(reverse(substr(name,2,7)))<=9999999))
);-- 最后一位必须为1,插入失败
insert into t(id,name) values(1,'a1111110');-- 插入成功
insert into t(id,name) values(1,'a1111111');-- 第一位为非字母,插入失败
insert into t(id,name) values(1,'01111111');-- 长度非8位(1位字母,7位数字),插入失败
insert into t(id,name) values(1,'a111111');-- 后7位为无效数字,插入失败
insert into t(id,name) values(1,'a111z111');
name 以a到Z之间的任一字母开头后面跟7个数字
[0-9]:0到9之间的任一数字
-- 或者这样:
drop table t purge;
create table t(
id number(18,0),
name varchar2(8),
constraint ck_1 check(regexp_like(name,'[A-z][0-9][0-9][0-9][0-9][0-9][0-9][1-9]'))
);
http://blog.csdn.net/minitoy/archive/2010/11/05/5990191.aspx
与
substr(name,2,7)>='0'2个含义是一样么?
-- 怎么会一样呢?
substr(name,2,7)>='0' AND substr(name,2,7)<='9')-- 上面语句的意思是 name从第2个字符开始,向后7个字符
-- (即第2到第8个字符内,可以有宽度不够的情况)
-- 其首字母必须在0到9之间!从第2个字符开始,后面的字符宽度没有限制为7(可多可少)-- 看下面的语句,你就应该明白啦:
eygle@SZTYORA> select ascii('a') from dual;ASCII('A')
----------
97eygle@SZTYORA> select ascii('abcdef') from dual;ASCII('ABCDEF')
---------------
97
[a-z,A-Z][0-9][0-9][0-9][0-9][0-9][0-9][0-9]
这个规则不对吧?
最后一位[1-9]吧?哦,就是11楼说的。
这位兄弟很有意思!
就快两颗星了,还真么激动。
呵呵。