-- ipaddr_str can describe 16 byte=32 half bytes including 15 colons =47 chars
CREATE function [dbo].[ip6ColTohex](@ipaddr_str char(64) )  
RETURNS binary(16)
AS  
BEGIN
    declare @erg_str         varchar(64); -- ipaddr_str without colons. max length: 16 * 2
    declare @erg_str1         varchar(64); -- ipaddr_str without colons. max length: 16 * 2
    declare @pos             integer;
    declare @found             integer;
    declare @len            integer;
    declare @len_part         integer;
    declare @partcount        integer;
    declare @found2colons    integer;
    declare @ipaddr_part     varchar(64); -- max 4 chars: ABCF
    declare @ipaddr_part2     varchar(64); -- max 4 chars: ABCF

    if (@ipaddr_str = NULL)
    begin
        -- Error
        -- raiserror('Error 9009: internal error: parameter NULL not allowed in ip6ColTohex', 16,1 );        
        return NULL;
    end

    set @len=len(@ipaddr_str);
    set @pos=1;
    set @erg_str='';
    set @erg_str1='';
    set @partcount=0;
    set @found2colons=0;

    -- check for correctness: no . allowed, mixed mode not supported.
    select @found=charindex('.',@ipaddr_str);
    if (@found > 0 )
    begin
        -- Error
        -- raiserror('Error 9009: internal error: illegal input for function ip6ColTohex', 16,1 );        
        return NULL;
    end

    -- check for correctness: at least one : expected mixed mode not supported.
    select @found=charindex(':',@ipaddr_str);
    if (@found = 0 )
    begin
        -- Error
        -- raiserror('Error 9009: internal error: illegal input for function ip6ColTohex', 16,1 );        
        return NULL;
    end


    while  (@pos < @len + 1 )
    begin

        select @found = charindex( ':', @ipaddr_str,  @pos )
        -- 0: end of string found, fetch the rest
        if @found = 0
            select @found=@len + 1;

        select @ipaddr_part=rtrim(ltrim(substring(@ipaddr_str,@pos,@found-@pos)));
        select @len_part=len(@ipaddr_part);

        if @len_part != 0
        begin
            select @ipaddr_part2=stuff('0000',4-@len_part+1,@len_part,@ipaddr_part);
            select @erg_str=@erg_str ++ @ipaddr_part2;
            set @partcount=@partcount + 1;
        end
        else
        begin
            -- support of short format ::
            -- :: is replaced by 0000... (see below)
            -- :: can only occur ones, otherwise NULL returned
            select @erg_str1=@erg_str;
            select @erg_str='';
            set @found2colons=@found2colons+1;
            -- :: at begin of string
            --if @pos = 1 and @found = 1 and substring(@rtrim(ltrim(@ipaddr_str)),@pos,2)= '::'
            --begin
            --    set @found2colons=1;
            --    set @erg_str1='0000';
            --end
            -- :: at end of string
            -- if @pos = len-1 && substring(@ipaddr_part,@pos,2)= '::'
        end

        -- pos to next part of ipaddr_str
        select @pos=@found + 1;

    end

    if @found2colons > 1
        return NULL;
    if @found2colons = 1
        select @erg_str=@erg_str1 ++ replicate('0000',8-@partcount) ++ @erg_str;
    if len(@erg_str) = 32
        return dbo.unhex (@erg_str);

    return NULL;
END
GO
GRANT EXECUTE ON  [dbo].[ip6ColTohex] TO [ca_itrm_group]
GRANT EXECUTE ON  [dbo].[ip6ColTohex] TO [ca_itrm_group_ams]
GO