Oracle DB-将输入数字设置为确切长度 [英] Oracle DB - Set Input Number to exact length

问题描述

我试图设置一个限制，在该限制中，只允许用户在数字数据类型"(电话号码)中输入11位数字.我已经尝试过

I am trying to set a constraint where user are only allowed to input 11 digits into the Number Data Type (Phone Number). I've tried

alter table "booyeah" add constraint
"booyeah_CC1" check ( LENGTH("PHONE_NO") = 11)
/

和

alter table "booyeah" add constraint
"booyeah_CC1" check ( PRECISION("PHONE_NO") = 11)
/

但出现错误.对于第一个，我一直出错，因为它没有检测到字符长度，而第二个却给了我一个无效的标识符.

but got an error. For the first one, I kept getting error because it's not detecting the character length, while the second one, gave me an invalid identifier.

感谢您的帮助！

推荐答案

将该列的数据类型设置为varchar(11).如果每次必须完全是11个字符，则检查约束将保证:check (length(phone_no) = 11).为了保证长度和数字"(全数字，无字母)，请使用

Set the data type for that column to varchar(11). If it must be exactly 11 characters every time, a check constraint will guarantee that: check (length(phone_no) = 11). To guarantee length and "numerality" (all digits, no letters), use

check (length(phone_no) = 11 and 
       regexp_like(phone_no, '^[[:digit:]]{11}$')
)

如果您必须使用数字类型-这是一个坏主意-您最好的选择可能是数字(11,0).

If you have to use a numeric type--and this is a bad idea--your best bet is probably numeric(11,0).

检查约束可以帮助您限制有效输入的范围，但是没有数字类型存储前导零.如果像00125436754这样的有效电话号码，您将不得不跳过不必要的和可避免的障碍.

A check constraint can help you restrict the range of valid input, but no numeric types store leading zeroes. You'll have to jump through unnecessary and avoidable hoops if something like 00125436754 is a valid phone number.

这篇关于Oracle DB-将输入数字设置为确切长度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！