Return to Revisions

Initially there are two problems with your conversion:

1. 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".

1. 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:

1. splitting on the colons
2. left-padding with "0"s
3. converting to BINARY(2)
4. 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.