Geek Answers Handbook

Convert signed int to string ip address in SQL Server

I retrieve the signed int from the SQL Server database and must convert it to a "normal" 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 for a while, but at the same time produces a stupid conclusion. For example, converting this to -1951276725 gives the result -116.-78.-30.-181 .

Any suggestions? Thanks.

+4
source share
3 answers
 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)

enter image description here

+8
source

The code you have will work if IPADDRESS was bigint (effectively storing the unsigned int representation in the database, i.e. all values> 0). Do you have the ability to change the data type in a table?

To get what you need, you need to convert the signed int to the equivalent unsigned int before converting to bigint. I'm not sure which is the most efficient way to do this in TSQL, but it could be disabling it in 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
source

As @Ed Harper stated that the selected solution does not work for a signed int. Below is my solution, which requires a little less casting and is not inverted. Check out the following test case, shown below, where the converted IP address of the / varchar line 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
0
source

More articles:


All Articles