CODEWITHSUNDEEP
sqlsql-server
asmgx
asmgx

Reputation: 8034

How to add extremely large numbers in SQL Server

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?

Upvotes: 1

Views: 646

Answers (3)

Martin Smith
Martin Smith

Reputation: 453628

As your input digits are restricted to 1 and 0 you can do the following.

First create a numbers table with at least as many rows in it as your longest string.

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

And then you can do

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

Upvotes: 1

Gert-Jan
Gert-Jan

Reputation: 347

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

Upvotes: 2

Jonas Metzler
Jonas Metzler

Reputation: 5975

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

Upvotes: 1

Related Questions