title: Perfecting SQL binary to IP Address string conversion
body: <p>We use a binary(16) field to store IP Addresses.  We do this as it can hold both IPv4 and IPv6 addresses and is easily used with the .Net IPAddress class.</p>
  
  <p>However I have created the  following SQL function to convert the binary address to the IP Address string for reporting purposes.</p>
  
  <pre><code>CREATE FUNCTION fn_ConvertBinaryIPAddressToString
  (
      @binaryIP binary(16)
  )
  RETURNS nvarchar(39)
  AS
  BEGIN
      DECLARE @ipAsString nvarchar(39)
  
      -- Is IPv4
      IF (substring(@binaryIP, 5, 1) = 0x00)  &lt;-- Is there a better way?
      BEGIN
  
          SELECT @ipAsString = CAST(CAST(substring(@binaryIP, 1, 1) AS int) AS nvarchar(3)) + '.' +
          CAST(CAST(substring(@binaryIP, 2, 1) AS int) AS nvarchar(3)) + '.' +
          CAST(CAST(substring(@binaryIP, 3, 1) AS int) AS nvarchar(3)) + '.' +
          CAST(CAST(substring(@binaryIP, 4, 1) AS int) AS nvarchar(3))
  
      END
      ELSE
      BEGIN
      -- Is IPv6
          -- taken and modified from http://support.microsoft.com/kb/104829
          DECLARE @i int
          DECLARE @length int
          DECLARE @hexstring char(16)
  
          SELECT @ipAsString = ''
          SELECT @i = 1
          SELECT @length = 16
          SELECT @hexstring = '0123456789ABCDEF'
  
          WHILE (@i &lt;= @length)
          BEGIN
              DECLARE @tempint int
              DECLARE @firstint int
              DECLARE @secondint int
  
              SELECT @tempint = convert(int, substring(@binaryIP,@i,1))
              SELECT @firstint = floor(@tempint/16)
              SELECT @secondint = @tempint - (@firstint*16)
  
              SELECT @ipAsString = @ipAsString + substring(@hexstring, @firstint+1, 1) + substring(@hexstring, @secondint+1, 1)
  
              IF (@i % 2 = 0)
                  SELECT @ipAsString = @ipAsString + ':'
  
              SELECT @i = @i + 1
         END
      END
  
      -- Return the result of the function
      RETURN @ipAsString
  
  END
  GO
  </code></pre>
  
  <p>At the moment if the 5 byte is 0 I assume it is an IPv4 address.  Is this a safe assumption? Is it better to check all the remaining bytes for zeros or is there a better way?</p>
  
  <p><strong>EDIT</strong> removed unnecessary cast</p>