SQL IPV6 to IPV4 conversion

From time to time during my regular job I get one off requests, this one was interesting enough to throw here for someone that may happen to find it via the interwebz.

I was asked about doing a script to convert ipv6 to ipv4 on the fly and at first I immediately said “no way, this just won’t work blah blah blah”… well turns out after a bit of time reading the spec sheet for ipv6 I saw this was quite do-able but definitely had some caveats.

The most major of the caveats is that ipv6 will have much higher ranges than can be converted back to the 255.255.255.255 range – which was the point of ipv6… higher numerical ranges.

I digress, here is the script – use at your own risk and never copy code from the internet and paste into your production environment without testing thoroughly.

CREATE FUNCTION [dbo].[f_ConvertIPV6to4]
(
@AddrIPV6 varchar(50)
)
RETURNS varchar(17)
AS
BEGIN

declare @ipv4 varchar(17)

set @ipv4 = (

select

cast(convert(int, convert(varbinary, ‘0x’ + substring(substring(@AddrIPV6, (len(@AddrIPV6)-charindex(‘:’, @AddrIPV6)) – 3, 4), 1, 2), 1)) as varchar(3)) + ‘.’ +

cast(convert(int, convert(varbinary, ‘0x’ + substring(substring(@AddrIPV6, (len(@AddrIPV6)-charindex(‘:’, @AddrIPV6)) – 3, 4), 3, 2), 1)) as varchar(3)) + ‘.’ +

cast(convert(int, convert(varbinary, ‘0x’ + substring(substring(@AddrIPV6, (len(@AddrIPV6)-charindex(‘:’, @AddrIPV6)) + 2, 4), 1, 2), 1)) as varchar(3)) + ‘.’ +

cast(convert(int, convert(varbinary, ‘0x’ + substring(substring(@AddrIPV6, (len(@AddrIPV6)-charindex(‘:’, @AddrIPV6)) + 2, 4), 3, 2), 1)) as varchar(3))

)

return @ipv4

END

It might look like a mess but it’s very fast and can be called inline during a table select; it can definitely be shortened but this was just a “quicky” script to get the job done.

For anyone trying to make sense of that this script actually does let me try to explain it as best as I can.

IPV6 format looks something like this:

2001:0:9d38:953c:c8d:ae7:b5de:abb7

The part in bold is our hex equivalent of a standard ipv4 ip address (unless it’s past the ipv4 range).

The script breaks out the last eight characters into hex chunks like so:

1) 0xb5
2) 0xde
3) 0xab
4) 0xb7

The conversion is completed on each above chunk into decimal and concatenated together with periods, voila, ipv4!

Let me know what you think about my masterpiece in the comments 😀

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s