将带符号的int转换为SQL Server中的字符串ip地址 [英] Convert signed int to string ip address in SQL Server

问题描述

我正在从SQL Server数据库中检索一个有符号的int，需要将其转换为看起来像普通"的点分字符串以显示给用户.

I'm retrieving a signed int from a SQL Server database and need to convert it to a "normal" looking dotted string for display to users.

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

Googling, I found this code:

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.

which works some of the time, but produces wacky output other times. For example, converting this -1951276725 yields the result -116.-78.-30.-181.

有什么建议吗?谢谢.

推荐答案

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)

这篇关于将带符号的int转换为SQL Server中的字符串ip地址的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！