On SQL Server 2017 I have a base 10 number of type DECIMAL(38,0)
which I need to convert to its base 2 representation.
This works fine for smaller numbers, but fails with the target order of magnitude:
DECLARE @var1 DECIMAL(38, 0) = 2679226456636072036565806253187530752; WITH A AS (SELECT @var1 AS NUMBER, CAST('' AS VARCHAR(125)) AS BITS UNION ALL SELECT CAST(ROUND(NUMBER / 2, 0, 1) AS DECIMAL(38, 0)), CAST(BITS + CAST(NUMBER % 2 AS VARCHAR(125)) AS VARCHAR(125)) FROM A WHERE NUMBER > 0) SELECT NUMBER, RIGHT(REPLICATE('0', 125) + CASE WHEN BITS = '' THEN '0' ELSE REVERSE(BITS) END, 125) AS BIN_VALUE FROM A WHERE NUMBER = 0;
The error message reads Msg 8115, Level 16, State 2, Line 2 Arithmetic overflow error converting expression to data type numeric.
I have also tried other approaches, but each has failed due to the magnitude of the original value. Any help with performing this conversion is greatly appreciated.
Advertisement
Answer
--DECLARE @var1 DECIMAL(38, 0) = 2679226456636072036565806253187530752; DECLARE @var1 DECIMAL(38, 0) = 99999567999999999999999999999999999999; WITH A AS ( SELECT @var1 AS NUMBER, CAST('' AS VARCHAR(130)) AS BITS UNION ALL SELECT CAST( cast(round(NUMBER*0.1, 0, 1) as decimal(38,0))*5 --high part + round(cast('0.'+right(NUMBER, 1) as decimal(10,2))* 5, 0, 1) --low part --ROUND(NUMBER * 0.5, 0, 1) AS DECIMAL(38, 0)), CAST(BITS + CAST(NUMBER % 2 AS VARCHAR(125)) AS VARCHAR(130)) FROM A WHERE NUMBER > 0 ) SELECT NUMBER, RIGHT(REPLICATE('0', 130) + CASE WHEN BITS = '' THEN '0' ELSE REVERSE(BITS) END, 130) AS BIN_VALUE FROM A WHERE NUMBER = 0 option (maxrecursion 0);