Recursive CTE for alpha/roman numeral encoding

numerals.sql

-- Encodes the provided numeral value using alpha numerals.
create or alter function encode_alpha(@numeral int, @a char(1)) returns varchar(32)
with returns null on null input as
begin
  declare @result varchar(32);

  with [alphabet] ([Value], [Char]) as (
    select [n].[Value], char(ascii(@a) + [n].[Value] - 1)
    from (values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26)) as [n] ([Value])
  ),
  [encoded] ([Quotient], [Char]) as (
    select @numeral, cast('!' as char(1))
    union all
    select ([encoded].[Quotient] - 1) / 26, [alphabet].[Char]
    from [encoded]
    join [alphabet] on [alphabet].[Value] = ([encoded].[Quotient] - 1) % 26 + 1
    where [Quotient] > 0
  )
  select @result = string_agg([Char], '') within group (order by [Quotient] asc)
  from [encoded]
  where [Quotient] != @numeral;

  return @result;
end
go

-- Encodes the provided numeral value using roman numerals.
create or alter function encode_roman(@numeral as int, @i as char(1)) returns varchar(32)
with returns null on null input as
begin
  declare @result varchar(32);
  
  with [alphabet] ([Value], [Char], [Power]) as (
    select cast([Value] as int), cast(char(ascii(@i) + ascii([Char]) - ascii('i')) as char(1)), cast([Power] as tinyint)
    from (values (1, 'i', 10), (5, 'v', 5), (10, 'x', 10), (50, 'l', 5), (100, 'c', 10), (500, 'd', 5), (1000, 'm', 10)) as [x] ([Value], [Char], [Power])
  ),
  [encoded] ([Subtractive], [Remainder], [Char]) as (
    select cast(0 as bit), @numeral, cast('!' as char(1))
    union all (
      select cast([x].[Subtractive] as bit), [x].[Remainder], [x].[Char]
      from [encoded] as [r]
      cross apply (
        -- Apply the subtractive rule: subtract a power of 10 from the value when doing so would permit
        -- you to express the value using the next quintile or decile, e.g. IV.
        select 1, [r].[Remainder] + [a].[Value] - [a].[Value] * IIF([a].[Value] * 9 <= [r].[Remainder], 10, 5), [a].[Char]
        from [alphabet] as [a]
        where [a].[Power] = 10 and [a].[Value] != 1000 and (
          [a].[Value] * 4 <= [r].[Remainder] and [r].[Remainder] < [a].[Value] * 5
          or [a].[Value] * 9 <= [r].[Remainder] and [r].[Remainder] < [a].[Value] * 10
        )
        union all
        -- Add the next quintile or decile to the subtracted value.
        select 0, [r].[Remainder] - [a].[Value] + [a].[Value] / [a].[Power], [a].[Char]
        from [alphabet] as [a]
        where [a].[Value] / [a].[Power] * ([a].[Power] - 1) <= [r].[Remainder] and [r].[Remainder] < [a].[Value]
        union all
        -- Regular additive rule, omitting values that are handled by the subtractive rule
        select 0, [r].[Remainder] - [a].[Value], [a].[Char]
        from [alphabet] as [a]
        where [a].[Value] <= [r].[Remainder] and (
          -- value cannot exceed that of the next quintile less this decile
          [a].[Power] = 10 and [r].[Remainder] < [a].[Value] * 4
          -- value cannot exceed that of the next decile less the preceding decile
          or [a].[Power] = 5 and [r].[Remainder] < [a].[Value] * 2 - [a].[Value] / 5
          -- the subtractive rule does not apply to M
          or [a].[Value] = 1000
        )
      ) as [x] ([Subtractive], [Remainder], [Char])
      where [r].[Remainder] > 0 and [r].[Subtractive] = 0
    )
  )
  select @result = string_agg([Char], '')
  from [encoded]
  where [Remainder] != @numeral;

  return @result;
end
go