检查字符串的约束以仅包含数字。(Oracle SQL)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1167767/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
CHECK CONSTRAINT of string to contain only digits. (Oracle SQL)
提问by Shannon Severance
I have a column, say PROD_NUM that contains a 'number' that is left padded with zeros. For example 001004569. They are all nine characters long.
我有一列，比如说 PROD_NUM，它包含一个用零填充的“数字”。例如 001004569。它们都是九个字符长。
I do not use a numeric type because the normal operation on numbers do not make sense on these "numbers" (For example PROD_NUM * 2 does not make any sense.) And since they are all the same length, the column is defined as a CHAR(9)
我不使用数字类型，因为对数字的正常操作对这些“数字”没有意义（例如 PROD_NUM * 2 没有任何意义。）而且由于它们的长度都相同，因此该列被定义为字符(9)
CREATE TABLE PRODUCT (
PROD_NUM CHAR(9) NOT NULL
-- ETC.
)
I would like to constrain PROD_NUM so it can only contain nine digits. No spaces, no other characters besides '0' through '9'
我想限制 PROD_NUM 所以它只能包含九位数字。没有空格，除了“0”到“9”之外没有其他字符
回答by Tim Sylvester
REGEXP_LIKE(PROD_NUM, '^[[:digit:]]{9}$')
回答by Rob van Wijk
You already received some nice answers on how to continue on your current path. Please allow me to suggest a different path: use a number(9,0) datatype instead.
您已经收到了一些关于如何继续当前道路的很好的答案。请允许我建议不同的路径：改用 number(9,0) 数据类型。
Reasons:
原因：
You don't need an additional check constraint to confirm it contains a real number.
You are not fooling the optimizer. For example, how many prod_num's are "BETWEEN '000000009' and '000000010'"? Lots of character strings fit in there. Whereas "prod_num between 9 and 10" obviously selects only two numbers. Cardinalities will be better, leading to better execution plans.
You are not fooling future colleagues who have to maintain your code. Naming it "prod_num" will have them automatically assume it contains a number.
您不需要额外的检查约束来确认它包含一个实数。
你不是在愚弄优化器。例如，“BETWEEN '000000009' 和 '000000010' 之间有多少个 prod_num”？很多字符串都适合在那里。而“prod_num between 9 and 10”显然只选择了两个数字。基数会更好，从而产生更好的执行计划。
您不会愚弄未来必须维护您的代码的同事。将其命名为“prod_num”将使他们自动假定它包含一个数字。
Your application can use lpad(to_char(prod_num),9,'0'), preferably exposed in a view.
您的应用程序可以使用 lpad(to_char(prod_num),9,'0')，最好在视图中公开。
Regards, Rob.
问候，罗布。
(update by MH) The comment thread has a discussion which nicely illustrates the various things to consider about this approach. If this topic is interesting you should read them.
（由 MH 更新）评论线程中有一个讨论，很好地说明了关于这种方法需要考虑的各种事情。如果这个话题很有趣，你应该阅读它们。
回答by David Aldridge
Works in all versions:
适用于所有版本：
TRANSLATE(PROD_NUM,'123456789','000000000') = '000000000'
回答by Theo
I think Codebender's regexp will work fine but I suspect it is a bit slow.
我认为 Codebender 的正则表达式可以正常工作，但我怀疑它有点慢。
You can do (untested)
你可以做（未经测试）
replace(translate(prod_num,'0123456789','NNNNNNNNNN'),'N',null) is null
替换（翻译（prod_num，'0123456789'，'NNNNNNNNNN'），'N'，null）为空
回答by danny
in MS SQL server I use this command: alter table add constraint [cc_mytable_myfield] check (cast(myfield as bigint) > 0)
在 MS SQL 服务器中我使用这个命令：alter table add constraint [cc_mytable_myfield] check (cast(myfield as bigint) > 0)
回答by Joel Coehoorn
Cast it to integer, cast it back to varchar, and check that it equals the original string?
将其转换为整数，将其转换回 varchar，并检查它是否等于原始字符串？
回答by richardtallent
In MSSQL, I might use something like this as the constraint test:
在 MSSQL 中，我可能会使用这样的东西作为约束测试：
PROD_NUM NOT LIKE '%[^0-9]%'
I'm not an Oracle person, but I don't think they support bracketed character lists.
我不是 Oracle 人员，但我认为他们不支持括号内的字符列表。
回答by blispr
Not sure about performance but if you know the range, the following will work. Uses a CHECK constraint at the time of creating the DDL.
不确定性能，但如果您知道范围，以下将起作用。在创建 DDL 时使用 CHECK 约束。
alter table test add jz2 varchar2(4)
check ( jz2 between 1 and 2000000 );
as will
随心所欲
alter table test add jz2 varchar2(4)
check ( jz2 in (1,2,3) );
this will also work
这也行
alter table test add jz2 varchar2(4)
check ( jz2 > 0 );
