如何在SQL Server中添加非常大的数字

Question

我在SQL Server中以字符串格式存储的数字

这些值只能在0和1之间

declare @a varchar(max) = '100101001010100111010001010101011101010110100001010010111001'
declare @b varchar(max) = '010101100101010010101101001010100010100011010000101000100010'

a和b的长度可以达到100000位

我想添加这两个变量作为数值

之类的

@a + @b 

结果应该是

110202101111110121111102011111111111110121110001111010211011

你可以看到这不是一个二进制加法…有2s

如何在SQL Server中做到这一点?

I tried this

declare @a varchar(max) = '100101001010100111010001010101011101010110100001010010111001'
declare @b varchar(max) = '010101100101010010101101001010100010100011010000101000100010'
declare @ai bigint = cast(@a as bigint)
declare @bi bigint = cast(@b as bigint)
SELECT @ai + @bi

但是我得到了这个错误

Msg 8115, Level 16, State 2, Line 4
将表达式转换为bigint数据类型时算术溢出错误。
Msg 8115, Level 16, State 2, Line 5
将表达式转换为bigint数据类型时发生算术溢出错误。

我该怎么做呢?

Answer 1

你必须创建你自己的计算器。

下面的代码考虑了一些复杂的因素:

• 在将数字转换为字符串时丢失前导零
• 在溢出的情况下，将最高有效数字从一个批次转移到下一个批次
例如:

declare @a varchar(max) = '100101001010100111010001010101011101010110100001010010311001'
declare @b varchar(max) = '010101100101010010101101001010100010100011010000101000900010'
declare @e varchar(max) = '110202101111110121111102011111111111110121110001111011211011'  -- expected
declare @r varchar(max) = ''  -- result
declare @batch_size int  -- amount of digits to process at once
set @batch_size=18
declare @sum varchar(19)  -- must be bigger than @batch_size
declare @carry bigint
set @carry = 0
declare @length int
set @length = LEN(@a)  -- assumes LEN(@a) = LEN(@b)
declare @i int
set @i = @length / @batch_size
if @length % @batch_size = 0
set @i = @i - 1
while @i >= 0 begin
if @i * @batch_size + @batch_size > @length begin
set @a = @a + REPLICATE('0', @batch_size - @length % @batch_size)
set @b = @b + REPLICATE('0', @batch_size - @length % @batch_size)
end

set @sum = CAST(SUBSTRING(@a, @i * @batch_size + 1, @batch_size) AS bigint)
+ CAST(SUBSTRING(@b, @i * @batch_size + 1, @batch_size) AS bigint)
+ @carry
set @carry = 0
if LEN(@sum) > @batch_size begin
set @carry = SUBSTRING(@sum, 1, 1)
set @sum = SUBSTRING(@sum, 2, @batch_size)
end

if LEN(@sum) < @batch_size
set @sum = REPLICATE('0', @batch_size - LEN(@sum)) + @sum

if @i * @batch_size + @batch_size > @length
set @sum = SUBSTRING(@sum, 1, @length - @i * @batch_size)

set @r = @sum + @r

set @i = @i - 1
end
if @carry > 0
print 'overflow error'
if @r <> @e
print 'not the correct result'
select substring(@r,1,@length) as sum_of_a_and_b

Answer 2

可以使用FLOAT(53):

declare @a varchar(max) = '100101001010100111010001010101011101010110100001010010111001'
declare @b varchar(max) = '010101100101010010101101001010100010100011010000101000100010'
declare @ai FLOAT(53) = cast(@a as FLOAT(53))
declare @bi FLOAT(53) = cast(@b as FLOAT(53))

SELECT @ai + @b的结果为1.1020210111111E+59这将适用于您的示例输入。但是100,000个数字作为数字数据类型是不可能的。

,db&lt的在小提琴

Answer 3

由于您的输入数字仅限于1和0，您可以执行以下操作:

首先创建一个数字表，其中至少有与最长字符串相同的行数。

CREATE TABLE dbo.Numbers(Number INT PRIMARY KEY WITH (DATA_COMPRESSION = ROW));
INSERT dbo.Numbers
SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY @@SPID)
FROM sys.all_columns c1, sys.all_columns c2

然后你可以写

declare @a varchar(max) = '100101001010100111010001010101011101010110100001010010111001'
declare @b varchar(max) =  '10101100101010010101101001010100010100011010000101000100010'
declare @c varchar(max) 
SELECT @c = STRING_AGG(0 + SUBSTRING(normalised.a, Number, 1) + SUBSTRING(normalised.B, Number, 1), '') WITHIN GROUP (ORDER BY Number)
FROM dbo.Numbers
CROSS APPLY(SELECT LEN(@a), LEN(@b)) lengths(len_a, len_b)
/*If @a and @b are not equal length add zeroes to left pad out the shorter one*/
CROSS APPLY (SELECT CONCAT(REPLICATE('0', len_b-len_a),@a), CONCAT(REPLICATE('0', len_a-len_b),@b)) normalised(a,b)
WHERE Number <= LEN(normalised.a)
PRINT @c