Initially there are two problems with your conversion:
It's the wrong "type" of conversion. You are converting a string into a hex / binary representation of the characters them selves. For example, a "26" will become 0x3236 in hex as 0x32 is the same as doing CHAR(0x32) and 0x36 is the same as CHAR(0x36), or it can be referred to as the ASCII value of the number "2". What you want is to simply change the form of the string "26" (which represents a byte) into 0x26. And for that you need to use a style number of "2" for the CONVERT function. For example:
SELECT CONVERT(VARBINARY(2), '26'), CONVERT(VARBINARY(2), '26', 2);
-- 0x3236 0x26
This is why you are getting 0x323630303A383830303A366130363A32 from 2600:8800:6a06:2000:1d29:3b7:8c7c:271b. You are really only getting the first 16 characters from that string, which are: 2600:8800:6a06:2. The two characters on the left I explained directly above. The 3 characters on the right, 6:2 are the 0x363A32 on the right side of the hex value, since 0x36 = "6", 0x3A = ":", and 0x32 = "2".
You will need to get rid of the colons as they are not valid binary digits. But the colons are there as delimiters for a reason: there are 8 segments, and if leading "0"s are removed, which is valid to do, then 0 really needs to be 0x0000, not 0x00 or 0x0. So, you need to ensure that each segment comes back as 4 hex digits.
You can accomplish all of this by:
- splitting on the colons
- left-padding with "0"s
- converting to
BINARY(2)
- concatenating those pieces together
If you are using SQL Server 2016 or newer, you can probably use the built-in STRING_SPLIT function. Or, if you are using a version prior to SQL Server 2016, you can either use whatever splitter you already have, or you can use the free version of my SQL# SQLCLR library (there are several string splitters in the Free version).
For example:
SELECT RIGHT('0000' + prt.[SplitVal], 4) AS [string],
CONVERT(BINARY(2), RIGHT('0000' + prt.[SplitVal], 4), 2) AS [hex]
FROM SQL#.String_Split4k('2600:100:400:0:0:0:0:0', ':', 1) prt;
returns:
string hex
2600 0x2600
0100 0x0100
0400 0x0400
0000 0x0000
0000 0x0000
0000 0x0000
0000 0x0000
0000 0x0000
That was just a way to see what the pieces are. To put them together into a single hex value, you can do the following:
DECLARE @IPv6 VARBINARY(16) = 0x;
SELECT @IPv6 += CONVERT(BINARY(2), RIGHT('0000' + prt.[SplitVal], 4), 2)
FROM SQL#.String_Split4k('2600:100:400:0:0:0:0:0', ':', 1) prt;
SELECT @IPv6;
-- 0x26000100040000000000000000000000
You can create a function to handle this conversion. You can then store these VARBINARY(16) values and create an index on them, and then convert the string coming in and store that in a variable to use in the query.
Here is that same approach, but using the built-in STRING_SPLIT function:
DECLARE @IPv6 VARBINARY(16) = 0x;
SELECT @IPv6 += CONVERT(BINARY(2), RIGHT('0000' + prt.[value], 4), 2)
FROM STRING_SPLIT('2600:100:400:0:0:0:0:0', ':') prt;
SELECT @IPv6;
-- 0x26000100040000000000000000000000
Just keep in mind that some folks are hesitant to use STRING_SPLIT in this type of situation because here the order that the split parts are returned is important as it can change the value if they came back in a different order, and the built-in function doesn't return a row number. I personally don't see how the returned values could come back out of order, but that is just an educated guess and not a guarantee. If you want a guarantee, then don't use the STRING_SPLIT built-in function.
P.S. The solution shown above (i.e. splitting on the colons) does not handle the "reduced" notation / syntax whereby "::" can be substituted for one or more consecutive ranges of "0000". For example: 2600:100:400::0. Hence, that solution requires that all 8 segments exist with at least "0" in them.
P.P.S. If you are using .NET either in the app or SQLCLR or SSIS, you can use a combination of IPAddress.TryParse and IPAddress.GetAddressBytes to handle the reduced "::" notation. Or, you could always write a bunch of code to handle it in pure T-SQL.
P.P.P.S. INET_IPv6toBinary will be available in the next release (version 4.3) of SQL# :-)
