sql-server - SQL Server 中将signed int 转换为字符串ip 地址

Question

我正在从 SQL Server 数据库检索一个有符号 int，并且需要将其转换为“正常”的点分字符串以向用户显示。

谷歌搜索，我找到了这段代码:

SELECT 
   dbo.IPADDRESS.IPADDRESS,
   CAST(ROUND( (cast(dbo.IPADDRESS.IPADDRESS as bigint) / 16777216 ), 0, 1) AS varchar(4)) + '.' +
   CAST((ROUND( (cast(dbo.IPADDRESS.IPADDRESS as bigint) / 65536 ), 0, 1) % 256) AS varchar(4)) + '.' +
   CAST((ROUND( (cast(dbo.IPADDRESS.IPADDRESS as bigint) / 256 ), 0, 1) % 256) AS varchar(4)) + '.' + 
   CAST((cast(dbo.IPADDRESS.IPADDRESS as bigint) % 256 ) AS varchar(4)) as IPDottedNotation
FROM  
   dbo.IPADDRESS

有时会起作用，但有时会产生奇怪的输出。例如，转换此 -1951276725 会产生结果 -116.-78.-30.-181。

有什么建议吗？谢谢。

Answer 1

DECLARE @IPADDRESS TABLE (
  IPADDRESS INT);

INSERT INTO @IPADDRESS
VALUES      (-1139627840),
            ( 1);

SELECT 
        LTRIM(CAST(SUBSTRING(IP,4,1) AS TINYINT)) + '.' + 
        LTRIM(CAST(SUBSTRING(IP,3,1) AS TINYINT)) + '.' + 
        LTRIM(CAST(SUBSTRING(IP,2,1) AS TINYINT)) + '.' + 
        LTRIM(CAST(SUBSTRING(IP,1,1) AS TINYINT))
FROM @IPADDRESS
CROSS APPLY (SELECT CAST(IPADDRESS AS BINARY(4))) C(IP)