Convert signed int to string ip address in SQL Ser

问题:

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

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

Any suggestions? Thanks.

回答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)

回答2:

The code you have would work if IPADDRESS was a bigint (effectively storing the unsigned int representation in the database - i.e. all values > 0). Do you have the option of changing the datatype in the table?

To get what you have to work, you need to convert your signed int to the equivalent unsigned int before the conversion to bigint. I'm not sure what the most efficient way to do this in TSQL is, but it might be to cast it to binary:

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

回答3:

Like @Ed Harper stated that the selected solution doesn't work for a signed int. Below is my solution which requires a little less casting and isn't inverted. Check out the following test scenario shown below where the converted string/varchar IP should be 192.168.18.188:

CREATE TABLE #data
  (
     ip     NVARCHAR(45),
     before NVARCHAR(45)
  )

INSERT INTO #data
VALUES     ('converted-ip','-1139627840')

    update #data
    set ip = cast((cast(before as int) & 255) as nvarchar) + '.' +
         cast((cast(floor(convert(decimal, before)/256) as int) & 255) as nvarchar) + '.' +
         cast((cast(floor(convert(decimal, before)/65536) as int) & 255) as nvarchar) + '.' +
         cast((cast(floor(convert(decimal, before)/16777216) as int) & 255) as nvarchar)

select * from #data