问题描述
如何使用 T-SQL 将二进制(基数 2 存储为 varchar)转换为十进制(基数 10 存储为 int 或 bigint),反之亦然?
How can I convert binary (base 2 stored as varchar) into decimal (base 10 stored as int or bigint) and the other way around, using T-SQL?
示例结果:
- 7 (dec) <--> 111 (bin)
- 136 (dec) <--> 10001000 (bin)
- 2123942362 (dec) <--> 1111110100110001100100111011010 (bin)
- 7 (dec) <--> 111 (bin)
- 136 (dec) <--> 10001000 (bin)
- 2123942362 (dec) <--> 1111110100110001100100111011010 (bin)
推荐答案
这个答案可以处理 bigint
This answer can handle bigint
转换为位(包含 1 和 0 的 varchar)
Convert to bit(varchar containing 1 and 0)
DECLARE @input BIGINT = 9223372036854775807 ;WITH N(N)AS ( SELECT top 63 POWER(cast(2 as bigint), ROW_NUMBER()over(ORDER BY (SELECT 1))-1) FROM (VALUES(1),(1),(1),(1))M(a), (VALUES(1),(1),(1),(1))L(a), (VALUES(1),(1),(1),(1))K(a) ) SELECT COALESCE ( REVERSE ( ( SELECT CAST(@input/N%2 as CHAR(1)) FROM N WHERE N <= @input for xml path(''), type ).value('.', 'varchar(max)') ) , '0' )
结果:
111111111111111111111111111111111111111111111111111111111111111
将包含位值的 varchar 转换为 bigint
Convert varchar containing bit values to bigint
DECLARE @input varchar(max) = '111111111111111111111111111111111111111111111111111111111111111' ;WITH N(V) AS ( SELECT ROW_NUMBER()over(ORDER BY (SELECT 1)) FROM (VALUES(1),(1),(1),(1))M(a), (VALUES(1),(1),(1),(1))L(a), (VALUES(1),(1),(1),(1))K(a) ) SELECT SUM(SUBSTRING(REVERSE(@input),V,1)*POWER(CAST(2 as BIGINT), V-1)) FROM N WHERE V <= LEN(@input)
结果:
9223372036854775807