17,377
社区成员
发帖
与我相关
我的任务
分享
-- 我想:这就是楼主所要的啦:
-- 或者这样:
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]'))
);
-- 或者这样:
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');
-- 或者这样:
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((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');
[Quote=引用 13 楼 thebesteric 的回复:]
substr(name,2,7)>='0' AND substr(name,2,7)<='9')
与
substr(name,2,7)>='0'
2个含义是一样么?
[/Quote]
-- 怎么会一样呢?
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')
----------
97
eygle@SZTYORA> select ascii('abcdef') from dual;
ASCII('ABCDEF')
---------------
97