本文介绍了在SQL Server中将以10为基数转换为以2为基数的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
在SQL Server 2017上,我有一个类型为DECIMAL(38,0)
的基数10,需要将其转换为基数2的表示形式.
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;
错误消息显示为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.
推荐答案
--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);
这篇关于在SQL Server中将以10为基数转换为以2为基数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!