Converting Integer to IP format
Posted: May 15, 2009 Filed under: SQL Server Leave a commentI came across this question at ASP.NET forum http://forums.asp.net/t/1423554.aspx.
User George Mastros (gmmastros) pointed the user to a post by Denis Gobo (SQLDenis) at lessthandot.com:
In this posting, SQLDenis provided two functions to convert between IP address and bigint number presentation of the IP.
The user was asking for an inline query to mimic the function after he read the posting.
Here is the conversion of Denis’s BigIntnumberToIp function:
–Test
declare @IP bigint SET @IP =4278058234 --254.253.252.250 SELECT (CONVERT(VARCHAR, @IP / 16777216) + '.' + CONVERT(VARCHAR, CAST(@IP - (@IP / 16777216 * 16777216) as INT) / 65536) + '.' + CONVERT(VARCHAR, CAST((CAST(@IP - (@IP / 16777216 * 16777216) as INT) - (CAST(@IP - (@IP / 16777216 * 16777216) as int) / 65536 * 65536)) as int) / 256) + '.' + CONVERT(VARCHAR, CAST((CAST(@IP - (@IP / 16777216 * 16777216) as int) - (CAST(@IP - (@IP / 16777216 * 16777216) as int) / 65536 * 65536)) as int) - (( CAST((CAST(@IP - (@IP / 16777216 * 16777216) as int) - (CAST(@IP - (@IP / 16777216 * 16777216) as int) / 65536 * 65536)) as int) / 256) * 256)))
By the way, here is the orginal function by SQLDenis:
CREATE FUNCTION dbo.IntegerToIPAddress (@IP AS BIGINT)
RETURNS VARCHAR(15)
AS
BEGIN
DECLARE @Octet1 BIGINT
DECLARE @Octet2 TINYINT
DECLARE @Octet3 TINYINT
DECLARE @Octet4 TINYINT
DECLARE @RestOfIP BIGINT
SET @Octet1 = @IP / 16777216
SET @RestOfIP = @IP – (@Octet1 * 16777216)
SET @Octet2 = @RestOfIP / 65536
SET @RestOfIP = @RestOfIP – (@Octet2 * 65536)
SET @Octet3 = @RestOfIP / 256
SET @Octet4 = @RestOfIP – (@Octet3 * 256)
RETURN(CONVERT(VARCHAR, @Octet1) + ‘.’ +
CONVERT(VARCHAR, @Octet2) + ‘.’ +
CONVERT(VARCHAR, @Octet3) + ‘.’ +
CONVERT(VARCHAR, @Octet4))
END
Advertisements