转换十进制 <-->二进制与 T-SQL

2021-09-10 00:00:00 tsql sql-server

如何使用 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

相关文章