如何将整数列中的数字限制为10位数 [英] how to restrict no of digits in an integer column to 10 digits

问题描述

如何在sql server 2008中将整数列中的数字限制为10位r2

解决方案

32位整数的最大值（int in SQL Server）是2 147 483 647，所以整数已经固有地最多为10位。



如果你使用的是32位整数字段，不需要特别限制它。



如果您正在使用64位整数字段，您可以在列上设置一个验证规则，说它不能去超过9 999 999 999.



在SQL语言中，可能是这样的：

  ALTER   TABLE  [YourTable] 
  ADD   CONSTRAINT  [Check_YourField]  CHECK （[YourColumn]< =  9999999999 ）

希望这有帮助。

你不能。

SQL中的一个整数是一种特定的数据类型，它保存一个4字节的值，因此介于
$ b $之间b -2,147,483,648和2,147,483,647

在任何情况下都不能超过十位数，即使这样，最上面的数字也必须是0,1或2.


如果你想存储一个完整的十位数字，那么你需要一个8字节值的BIGINT，但是你也不能将它的长度限制为十位数。



我强烈建议您在源头限制输入，而不是尝试使用DB来执行此操作 - 输入验证毕竟是用户界面的一部分。

如果你试图用这个来存储电话号码，那么根本不要使用数字格式：改用NVARCHAR。数字列用于您可以合理地使用数值的值：并且将两个电话号码添加在一起没有任何意义。此外，电话号码可能包含非数字字符：[phone]是有效的电话号码。

这可以通过为该列添加CHECK约束来完成。



类似于 -

  ALTER  < span class =code-keyword> TABLE  YourTableName 
  ADD   CHECK （ YourColumnName< =  9999999999   AND  YourColumnName> =  -  9999999999）

但它仍然是一个不正确的用法，因为SQL Server中的最大int大小是2147483647.

所以你可能需要验证你的检查条件。



点击此链接了解更多关于CHECK约束的信息 -

SQL CHECK Constraint [ ^ ]

或

SQL SERVER - 在列上创建检查约束 [ ^ ]



希望，它有助于：）

how to restrict no of digits in an integer column to 10 digits in sql server 2008 r2

解决方案

The maximum value for a 32-bits integer (int in SQL Server) is 2 147 483 647, so an integer number is already intrinsically maxed to 10 digits.

If you are working with a 32-bits integer field, you do not need to restrict it specifically.

If you are working with a 64-bits integer field, you can setup a validation rule on the column saying it must not go over 9 999 999 999.

In SQL language, that could be something like:

ALTER TABLE [YourTable]
ADD CONSTRAINT [Check_YourField] CHECK ([YourColumn] <= 9999999999)

Hope this helps.

You can't.
An integer in SQL is a specific data type, which holds a 4 byte value, and is thus between
-2,147,483,648 and 2,147,483,647
It cannot hold more than ten digits under any circumstances, and even then the topmost digit must be 0, 1, or 2.

If you want to store a full ten digits, then you would need a BIGINT which is an 8 byte value, but you can't restrict the length of that to ten digits either.

I would strongly suggest that you restrict your inputs at source, rather than trying to use you DB to do it - input validation is part of the user interface, after all.
And if you are trying to use this to store telephone numbers then don;t use a numeric format at all: use a NVARCHAR instead. Numeric columns are for values you can sensibly do maths with: and adding two telephone numbers together does not make any sense. Additionally, telephone numbers may contain non-numeric characters: "[phone]" is a valid phone number.

This can be done by adding a CHECK Constraint for that column.

Something like-

ALTER TABLE YourTableName
ADD CHECK (YourColumnName <= 9999999999 AND YourColumnName >=-9999999999)

But it is still an incorrect use as max size of int in SQL Server is 2147483647.
So may be you have to verify your check condition.

Follow this link to know more about CHECK constraint-
SQL CHECK Constraint[^]
or
SQL SERVER – Create Check Constraint on Column[^]

Hope, it helps :)

这篇关于如何将整数列中的数字限制为10位数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！