How to add extremely large numbers in SQL Server-CodePudding

I have numbers in SQL Server that stored in string format

These values are in 0 and 1s only

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

a and b length can reach 100,000 digits

I want to add these 2 variables as numeric

Something like

@a   @b

And the result should be

 110202101111110121111102011111111111110121110001111010211011

You can see this is not a binary adding... there are 2s

How can I do this in 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

but I got this error

Msg 8115, Level 16, State 2, Line 4
Arithmetic overflow error converting expression to data type bigint.
Msg 8115, Level 16, State 2, Line 5
Arithmetic overflow error converting expression to data type bigint.

How can I do that?

CodePudding user response：

You can use 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))

The result of SELECT @ai @b will be 1.1020210111111E 59 This will work for your sample input. But 100.000 digits will be impossible as numeric data type.

db<>fiddle

CodePudding user response：

You'd have to create your own calculator.

Some complicating factors, that the code below takes into consideration:

losing leading zeroes when converting number to string
carry over most significant digit from one batch to the next in case of overflow

For example:

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