Using T-SQL to Decode a Decimal (BIGINT) IP Address

A site I ran across stored an IP Address as a BIGINT decimal number, to reveal the more friendly IP Address use either of the following methods to extract the octets or the Friendly IP. These formulae will allow you to return the IP Address in a usable manner.

IPAddress	IPOCTET1	IPOCTET2	IPOCTET3	IPOCTET4	IPAddressEX
2886730061	172	16	1	77	172.16.1.77
2886730059	172	16	1	75	172.16.1.75
2886730053	172	16	1	69	172.16.1.69
2886730046	172	16	1	62	172.16.1.62
2886730047	172	16	1	63	172.16.1.63
2886730303	172	16	2	63	172.16.2.63

,[IPAddress]

You can use:

,
   CAST(
  CAST([IPAddress]/POWER(256,3) AS BIGINT)
  AS VARCHAR(3)
   ) AS IPOCTET1
   ,
   CAST(
    CAST(
   CAST(
    [IPAddress]-CAST([IPAddress]/POWER(256,3) AS BIGINT)*POWER(256,3)
    AS BIGINT)/POWER(256,2) AS BIGINT)
    AS VARCHAR(3)) AS IPOCTET2
   ,
   CAST(
    CAST(
   CAST([IPAddress]
    -CAST([IPAddress]/POWER(256,3) AS BIGINT)*POWER(256,3)
    -CAST(CAST([IPAddress]-CAST([IPAddress]/POWER(256,3) AS BIGINT)*POWER(256,3)
    AS BIGINT)/POWER(256,2) AS BIGINT)*POWER(256,2) AS INTEGER)/256 AS INTEGER)  
  AS VARCHAR(3)) AS IPOCTET3
   ,
   CAST(
  [IPAddress]-
  (CAST([IPAddress]/POWER(256,3) AS BIGINT)*POWER(256,3)+
        CAST(CAST([IPAddress]-CAST([IPAddress]/POWER(256,3) AS BIGINT)*POWER(256,3) AS BIGINT)/POWER(256,2) AS BIGINT)*POWER(256,2)+
  CAST(
   CAST(
    [IPAddress]
    -CAST([IPAddress]/POWER(256,3) AS BIGINT)*POWER(256,3)
    -CAST(CAST([IPAddress]-CAST([IPAddress]/POWER(256,3) AS BIGINT)*POWER(256,3)
    AS BIGINT)/POWER(256,2) AS BIGINT)*POWER(256,2) AS BIGINT)/256 AS BIGINT)*256)
   AS VARCHAR(3)) AS IPOCTET4

or use this to build a properly concatenated string.

,  
   CAST(
  CAST([IPAddress]/POWER(256,3) AS BIGINT)
  AS VARCHAR(3)
   ) + '.' +
   CAST(
    CAST(
   CAST(
    [IPAddress]-CAST([IPAddress]/POWER(256,3) AS BIGINT)*POWER(256,3)
    AS BIGINT)/POWER(256,2) AS BIGINT)
    AS VARCHAR(3)) + '.' +
   CAST(
    CAST(
   CAST([IPAddress]
    -CAST([IPAddress]/POWER(256,3) AS BIGINT)*POWER(256,3)
    -CAST(CAST([IPAddress]-CAST([IPAddress]/POWER(256,3) AS BIGINT)*POWER(256,3)
    AS BIGINT)/POWER(256,2) AS BIGINT)*POWER(256,2) AS INTEGER)/256 AS INTEGER)  
  AS VARCHAR(3)) + '.' +
   CAST(
  [IPAddress]-
  (CAST([IPAddress]/POWER(256,3) AS BIGINT)*POWER(256,3)+
        CAST(CAST([IPAddress]-CAST([IPAddress]/POWER(256,3) AS BIGINT)*POWER(256,3) AS BIGINT)/POWER(256,2) AS BIGINT)*POWER(256,2)+
  CAST(
   CAST(
    [IPAddress]
    -CAST([IPAddress]/POWER(256,3) AS BIGINT)*POWER(256,3)
    -CAST(CAST([IPAddress]-CAST([IPAddress]/POWER(256,3) AS BIGINT)*POWER(256,3)
    AS BIGINT)/POWER(256,2) AS BIGINT)*POWER(256,2) AS BIGINT)/256 AS BIGINT)*256)
   AS VARCHAR(3)) AS IPAddressEX

...and Back

To find a printer, in this example by searching for one in SQL, that IP Address might seem fairly useless looking at the numbers involved. You could calculate it yourself by taking the Octets of the IP Address and using this formula:

16777216*Octet1 + 65536*Octet2 + 256*Octet3 + Octet4

...but we like automation.

DECLARE @IPAddr varchar(16);
DECLARE @IPAddrAsDecimal BIGINT;
DECLARE @IPAddrOctet1 BIGINT;
DECLARE @IPAddrOctet2 BIGINT;
DECLARE @IPAddrOctet3 BIGINT;
DECLARE @IPAddrOctet4 BIGINT;
DECLARE @intStartPos INT;
DECLARE @intEndPoint INT

SET @IPAddr='10.176.24.138';
--SET @IPAddr='172.16.2.66';

SELECT @IPAddrOctet1=CONVERT(BIGINT, LEFT(@IPAddr,CHARINDEX('.', @IPAddr)-1));

SELECT @intStartPos=(LEN(@IPAddrOctet1)+2);
SELECT @intEndPoint=CHARINDEX('.', @IPAddr, @intStartPos);
SELECT @IPAddrOctet2=CONVERT(BIGINT, SUBSTRING(@IPAddr,@intStartPos,@intEndPoint-@intStartPos));

SELECT @intStartPos=(LEN(@IPAddrOctet1)+LEN(@IPAddrOctet2)+3);
SELECT @intEndPoint=CHARINDEX('.', @IPAddr, @intStartPos);
SELECT @IPAddrOctet3=CONVERT(BIGINT, SUBSTRING(@IPAddr,@intStartPos,@intEndPoint-@intStartPos));

SELECT @intStartPos=(LEN(@IPAddrOctet1)+LEN(@IPAddrOctet2)+LEN(@IPAddrOctet3)+4);
SELECT @intEndPoint=LEN(@IPAddr);
SELECT @IPAddrOctet4=CONVERT(BIGINT, RIGHT(@IPAddr,@intEndPoint-@intStartPos+1));

SELECT @IPAddrOctet1 as Octet1, @IPAddrOctet2 as Octet2, @IPAddrOctet3 as Octet3, @IPAddrOctet4 as Octet4

SELECT @IPAddrAsDecimal=16777216*@IPAddrOctet1 + 65536*@IPAddrOctet2 + 256*@IPAddrOctet3 + @IPAddrOctet4;

SELECT @IPAddrAsDecimal as IPAddressAsDecimal;

--/* 

SELECT [IPAddress]
      ,[PrinterName]
      ,[PModelID]
      ,[SiteID]
      ,[MAC]
      ,[Location]
      ,[Approved]
      ,[Hits]
      ,[Agent]
      ,[TouchDate]
  FROM [oad].[dbo].[PrinterList]
WHERE IPAddress = @IPAddrAsDecimal
;
--*/