SQL Server中具有最大长度的数字的数据类型

I want to store number that can be of length more than 20 digit but not has maximum length defined.

我想存储长度超过20位但未定义最大长度的数字。

I search for the datatype that hold maximum value entered and found varchar(max), I want to have something like numeric(max) but that is not there.

我搜索保持输入最大值并找到varchar(max)的数据类型,我希望有类似数字(max)的东西,但那不存在。

What should I use, is it fine to use varchar(max) but then how can I validate for number?

我应该使用什么,使用varchar(max)是否合适但是如何验证数字?

3 个解决方案

#1

is it fine to use varchar(max) but then how can I validate for number?

是否可以使用varchar(max)但是如何验证数字?

You can use a check constraint:

您可以使用检查约束:

CREATE TABLE Numbers (
     n varchar(max) not null,
     constraint CK_Numeric CHECK (n not like '%[^0-9]%')
)

The check constraint is ensuring that no character within the n column is from outside of the range 0-9. It's a double negative, but it's the easiest way to express the condition. If the values can include decimals, you might want a more complex check:

检查约束确保n列中的任何字符都不在0-9范围之外。这是一个双重否定,但它是表达这种情况的最简单方法。如果值可以包含小数,则可能需要更复杂的检查:

CREATE TABLE Decimals (
     d varchar(max) not null,
     constraint CK_Numeric CHECK (
        d not like '%[^0-9.]%' AND
        LEN(d) - LEN(REPLACE(d,'.','')) in (0,1))
)

Which is saying that . is now allowed, and the LEN check is ensuring it only appears 0 or 1 times in the string.

这就是说。现在允许,LEN检查确保它只在字符串中出现0或1次。

#2

bigint has the range of -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807), so may suit your needs.

bigint的范围为-2 ^ 63(-9,223,372,036,854,775,808)至2 ^ 63-1(9,223,372,036,854,775,807),因此可能符合您的需求。

There are no larger integer types in SQL Server, so you may have to settle for a character based field (with all the conversions and sorting issues associated with storing numeric data in a text field).

SQL Server中没有更大的整数类型,因此您可能必须满足基于字符的字段(包含与在文本字段中存储数字数据相关的所有转换和排序问题)。

#3

According to this,

根据这个,

the datatype "float" definetly has the largest number support and supports numbers with 309 (!!!) digits.

数据类型“float”definetly具有最大数量的支持,并支持309(!!!)位数字。

It depends if your number is integer, real, negative possible ..

这取决于你的数字是整数,真实,负数可能..

Think really deeply if you are planning to use a varchar(max) to store numbers. The type of the columns in the database is (for me) the most important point to ensure the data consistency. So it is possible but is it really necessary?

如果您打算使用varchar(max)存储数字,请深入思考。数据库中列的类型(对我而言)是确保数据一致性的最重要的一点。所以它有可能但是真的有必要吗?

#1