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
1. left-padding with "0"s
1. converting to `BINARY(2)`
1. 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#][1] 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:

<!-- language: lang-none -->

    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:

<!-- language: lang-sql -->

    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][2] and [IPAddress.GetAddressBytes][3] 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#][4] :-)


  [1]: https://SQLsharp.com/?ref=db_230787
  [2]: https://learn.microsoft.com/en-us/dotnet/api/system.net.ipaddress.tryparse
  [3]: https://learn.microsoft.com/en-us/dotnet/api/system.net.ipaddress.getaddressbytes
  [4]: https://sqlsharp.com/?ref=db_230793