In MS SQL Server 2016 T-SQL, convert large hexadecimal to decimal (numeric(38)) and back

__tsql-hex-conversion-README.md

This is some code I wrote in a doomed attempt to deal with some long hex strings in some of our database objects.

The effort was doomed, but this code seems to work fine.

The strings I was working with were fixed 24-hex-digit strings, but you could probably make this variable length (so long as the values fit in numeric(38) values).

This was for Sql Server 2016, so I didn't have string_split() or string_agg(). (String_split doesn't take empty string or null for separator.)

fn_fromHex.sql

create or alter function dbo.fn_fromHex(
    @hexStr char(24)
, @powers dbo.udt_tbl_powers READONLY       -- Result of fn_powers()
)
    returns
        numeric(38)
as
    /**
      Converts a 24-digit hexadecimal number (e.g., Sisense oid) to decimal so math can be performed on it (e.g., +1).
     */
begin
    --         declare @characters dbo.udt_tbl_characterPositions = dbo.fn_stringToTableOfCharsIndexedFromRight ( upper( @hexStr));
--         declare @invalid_count int = (select count(*)
--                                           from @characters c
--                                           where 1 = 0
--                                              or c.character < '0'
--                                              or c.character > 'F'
--                                              or (c.character > '9' and c.character < 'A'))
--         if (@invalid_count <> 0)
--             begin
--                 raiserror ('Unexpected character in string', 16, 1) -- throw 50000, 'Unexpected character in string', 1
--             end
    declare @retval numeric(38);
    select @retval = sum(p.power *
                         case
                             when c.character >= '0' and c.character <= '9'
                                 then ascii(c.character) - 48 -- 48 = ascii('0')
                             when c.character >= 'A' and c.character <= 'F'
                                 then ascii(c.character) - 65 + 10 -- 65 = ascii('A')
                             when c.character > 'a' and c.character <= 'f'
                                 then ascii(c.character) - 97 + 10 -- 97 = ascii('a')
                             else
                                 0 -- throw 50000, 'Unexpected character "' + c.character + '" at position ' + c.position, 1
                             end
        )
        from @powers                                                              p
                 join dbo.fn_stringToTableOfCharsIndexedFromRight(upper(@hexStr)) c on p.exponent = c.position;
    return @retval
end;

fn_powers.sql

create or alter function dbo.fn_powers(@base numeric(38), @count int)
    /*
     Returns a table of @count computed powers of the given @base, from 0 to @count-1.
     Column "exponent" has values 0..@count-1.
     Column "power" has the result of @base raised to the "exponent" power.

     The intent is that, if you are converting a lot of hex numbers back and forth, you construct this table only once.
     */
    returns @powers table
                    (
                        exponent numeric(38) primary key,
                        power    numeric(38)
                    )
as
begin
    declare @i int = 0
    while @i < @count
        begin
            insert @powers (exponent, power) values (@i, power(@base, @i))
            set @i = @i + 1
        end
    return;
end

fn_stringToTableOfCharsIndexedFromRight.sql

create or alter function dbo.fn_stringToTableOfCharsIndexedFromRight(@str varchar(24))
    returns @tbl table
                 (
                     position  int primary key,
                     character char
                 ) as
    /**
      Returns a table consisting of one row for each character in the given string, with the position (0-based) of the
      character indexed from the right end of the string.

      (For hexadecimal numbers, the position index is the power of 16 corresponding to each digit position.)
     */
begin
    declare @i int = 0;
    declare @strLen int = len(@str)
    declare @character char;
    while @i < @strLen
        begin
            set @character = substring(@str, @strLen - @i, 1)
            insert @tbl (position, character) values (@i, @character)
            set @i = @i + 1
        end
    return
end

fn_toHex.sql

create or alter function dbo.fn_toHex(
    @number numeric(38)
, @powers dbo.udt_tbl_powers READONLY       -- Result of fn_powers().
)
    returns
        varchar(24)
--         @debugOutput table
--                      (
--                          position    int,
--                          divisor     numeric(38),
--                          quotient    numeric(38),
--                          scratch     numeric(38),
--                          digit       char,
--                          finalResult varchar(24)
--                      )
as
    /**
      Converts a large decimal number (assumed to fit within 24 hex digits, though) to a hexadecimal string.
     */
begin
    declare @hexDigits table
                       (
                           position int primary key,
                           digit    char
                       );
    declare @scratch numeric(38) = @number;
    -- For @position, could optimize by setting to (select max(p.exponent) from @powers p where p.power < @number),
    -- but I happen to know we'll be dealing with large numbers, so no need at this time.
    declare @position int = 23;
    declare @quotient numeric(38)
    declare @divisor numeric(38)
    declare @digit char;
    while @position >= 0
        begin
            set @divisor = (select top (1) p.power from @powers p where p.exponent = @position);
            set @quotient = floor( @scratch / @divisor);
            set @scratch = @scratch % @divisor;
            set @digit = case
                             when @quotient < 10 then char(48 + @quotient)
                             else char(97 - 10 + @quotient)
                end;
            insert @hexDigits (position, digit) values (@position, @digit)
--             insert @debugOutput (position, divisor, quotient, scratch, digit)
--                 values (@position, @divisor, @quotient, @scratch, @digit)
            set @position = @position - 1
        end
    -- And now we do Something Horrible: select the characters into a JSON string and munge it up in place.
    declare @digitString varchar(max);
    set @digitString =
            (select hd.digit d from @hexDigits hd order by hd.position desc for json path, without_array_wrapper);
    -- @digitString looks like this: {"d":"a"},{"d":"b"},{"d":"c"}
    set @digitString = replace(replace(@digitString, '{"d":"', ''), '"},', '');
    -- @digitString looks like this: abc"}
    set @digitString = substring(@digitString, 1, len(@digitString) - 2);
--     insert @debugOutput (finalResult) values (@digitString)
    return cast(@digitString as varchar(24));
end;

udt_tbl_characterPositions.sql

create type dbo.udt_tbl_characterPositions as table
(
    position int primary key,
    character char
);

udt_tbl_powers.sql

create type dbo.udt_tbl_powers as table
(
    exponent numeric(38) primary key ,
    power    numeric(38)
)

Prolly shouldn't use max here, maybe varchar(240) or so?