Question

求救:oracle的手机号判断怎么写？

手机号格式为11位，第一位为1，第二位为3或5，其他位为0到9，怎么用oracle写？
这个不对啊？check约束不是这种格式，我试过了，不行的。这个我刚搜到过了。

Answer 1

ORACLE 貌似用不了正则表达式，试试这个语句吧，我测过了

ALTER TABLE table_name DROP CONSTRAINT constraint_name;

ALTER TABLE table_name ADD CONSTRAINT constraint_name
CHECK (SUBSTR(column,1,1) = '1'
and SUBSTR(column,2,1) IN ('3','5')
and ASCII(SUBSTR(column,3,1)) between 48 and 57 and SUBSTR(column,3,1) is NOT NULL
and ASCII(SUBSTR(column,4,1)) between 48 and 57 and SUBSTR(column,4,1) is NOT NULL
and ASCII(SUBSTR(column,5,1)) between 48 and 57 and SUBSTR(column,5,1) is NOT NULL
and ASCII(SUBSTR(column,6,1)) between 48 and 57 and SUBSTR(column,6,1) is NOT NULL
and ASCII(SUBSTR(column,7,1)) between 48 and 57 and SUBSTR(column,7,1) is NOT NULL
and ASCII(SUBSTR(column,8,1)) between 48 and 57 and SUBSTR(column,8,1) is NOT NULL
and ASCII(SUBSTR(column,9,1)) between 48 and 57 and SUBSTR(column,9,1) is NOT NULL
and ASCII(SUBSTR(column,10,1)) between 48 and 57 and SUBSTR(column,10,1) is NOT NULL
and ASCII(SUBSTR(column,11,1)) between 48 and 57 and SUBSTR(column,11,1) is NOT NULL
);

小写字母的需要楼主替换。

Answer 2

case when To_number( (SUBSTR(column,1,2))=13 or To_number( (SUBSTR(column,1,2))=15
then (case when To_number( (SUBSTR(column,3,9))<999999999 then '手机号' else '其他' end) else '其他' end

Answer 3

ALTER TABLE table_name ADD CONSTRAINT constraint_name
CHECK(REGEXP_LIKE(Telephone,‘^1\[35]\[[:digit:]]{9}’));
应该是这样的，你试试，有问题再问我