CODEWITHSUNDEEP
sqlsql-serversql-server-2005ipv6
user1367200
user1367200

Reputation: 300

How to read an IPv6 address, format in full expanded notation, then convert to binary?

I'm using SQL Server 2005.

I'm trying to figure out how to read an IPv6 address stored as a string, then expand it into it's full 8 octet notation, and convert each octet into binary, and then add all the binary representations together to finally convert the address to binary.

The is the full notation: 2001:0db8:85a3:0000:0000:8a2e:0370:7334 Short notation for same as it is stored in our database: 2001:0db8:85a3::8a2e:0370:7334

Here is an example of another valid IPv6 address: ::1

Wikpedia has the rules nicely laid out and explained here: http://en.wikipedia.org/wiki/IPv6#Address_format

I'm not sure how to expand an IPv6 address to full notation and was hoping someone on here already has it figured out or knows how to do so. I googled this for hours but could not find any solution for SQL server 2005.

Once the address is expanded, I was thinking of splitting each octet based on the : and use the method outlined here to convert to binary: http://blogs.msdn.com/b/sqltips/archive/2008/07/02/converting-from-hex-string-to-varbinary-and-vice-versa.aspx

Any help much appreciated!

Upvotes: 1

Views: 2232

Answers (1)

RichardTheKiwi
RichardTheKiwi

Reputation: 107706

SQL Fiddle

Sample table:

select '2001:0db8:85a3:0000:0000:8a2e:0370:7334' address
  into ipv6
  union all
select '2001:db8:85a3::8a2e:0370:7334' union all
select '2001:0db8:85a3::' union all
select '::ff01:0db8:85a3' union all
select '::1' union all
select '002::' union all
select '::' union all
select '1:2:3:4:5:6:7:' union all
select ':A:2:3:4:5:6:7' union all
select 'F:2:3:4:5:6::8' union all
select '' union all -- invalid
select null; -- null test

Query:

with step1(address,full8) as(
     select address,
            replace(nullif(address,''),'::',replicate(':',9-len(address)+len(replace(address,':',''))))
       from ipv6
), step2(address,full8,xml) as (
     select address, full8,
            cast('<x>'+replace(full8,':','</x><x>')+'</x>' as xml)
       from step1
), step3(address,full8,xml,part,pos) as (
     select address, full8,xml,
            right('0000'+part.value('.','varchar(4)'),4),
            part.value('for $s in . return count(../*[. << $s]) + 1', 'int') pos
       from step2
cross apply xml.nodes('x') node(part)
), step4(address,hex) as (
     select o.address, (select i.part+''
                          from step3 i
                         where i.address=o.address
                      order by i.pos
                           for xml path('')) hex
       from step3 o
group by address
)
     select address,
            hex,
            cast('' as xml).value(
              'xs:hexBinary(sql:column("hex"))','binary(16)') bin
       from step4
   order by address;

Results:

|                                 ADDRESS |                              HEX |                                               BIN |
----------------------------------------------------------------------------------------------------------------------------------
|                                      :: | 00000000000000000000000000000000 |                   0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0 |
|                                     ::1 | 00000000000000000000000000000001 |                   0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1 |
|                        ::ff01:0db8:85a3 | 00000000000000000000ff010db885a3 |          0,0,0,0,0,0,0,0,0,0,-1,1,13,-72,-123,-93 |
|                          :A:2:3:4:5:6:7 | 0000000A000200030004000500060007 |                  0,0,0,10,0,2,0,3,0,4,0,5,0,6,0,7 |
|                                   002:: | 00020000000000000000000000000000 |                   0,2,0,0,0,0,0,0,0,0,0,0,0,0,0,0 |
|                          1:2:3:4:5:6:7: | 00010002000300040005000600070000 |                   0,1,0,2,0,3,0,4,0,5,0,6,0,7,0,0 |
|                        2001:0db8:85a3:: | 20010db885a300000000000000000000 |          32,1,13,-72,-123,-93,0,0,0,0,0,0,0,0,0,0 |
| 2001:0db8:85a3:0000:0000:8a2e:0370:7334 | 20010db885a3000000008a2e03707334 | 32,1,13,-72,-123,-93,0,0,0,0,-118,46,3,112,115,52 |
|           2001:db8:85a3::8a2e:0370:7334 | 20010db885a3000000008a2e03707334 | 32,1,13,-72,-123,-93,0,0,0,0,-118,46,3,112,115,52 |
|                          F:2:3:4:5:6::8 | 000F0002000300040005000600000008 |                  0,15,0,2,0,3,0,4,0,5,0,6,0,0,0,8 |

Note: SQL Fiddle doesn't display binary data very well...

Upvotes: 1

Related Questions