How to Convert Numbers to Roman Numerals in SQL Server

Hey guys!
Good morning!

In this post I will demonstrate how to convert a normal number (base 10) to a Roman numeral (X, XVII, etc.) using a Transact-SQL function in SQL Server that I found on the blog. Advaldo Paiva. Because it is a function, we can apply it for a simple conversion or to update data in a table, for example.

Function code:

CREATE FUNCTION [dbo].[fncConverte_Para_Romanos] (
    @Numero BIGINT
)
RETURNS VARCHAR(MAX)
BEGIN

    DECLARE @s varchar(MAX), @r varchar(MAX), @i bigint, @p bigint, @d bigint
     
    SET @s = ''
    SET @r = 'IVXLCDM' -- Simbolo Romanos
     
     IF @numero=0
        SET @s = '0'
        
     ELSE BEGIN
     
        SELECT @p = 1, @i = ABS(@numero)
        WHILE(@p<=5)
        BEGIN
            SET @d = @i % 10
            SET @i = @i / 10
            SELECT @s = CASE WHEN @d IN (0,1,2,3) THEN REPLICATE(SubString(@r,@p,1),@d) + @s
                             WHEN @d IN (4) THEN SUBSTRING(@r,@p,2) + @s
                             WHEN @d IN (5,6,7,8) THEN SUBSTRING(@r,@p+1,1) + REPLICATE(SubString(@r,@p,1),@d-5) + @s
                             WHEN @d IN (9) THEN SUBSTRING(@r,@p,1) + SubString(@r,@p+2,1) + @s
                        END
     
            SET @p = @p + 2
            
        END
        
        SET @s = Replicate('M',@i) + @s
     
        IF @numero < 0
            SET @s = '-' + @s
            
     END
     
     RETURN @s
     
END

Examples of use:

That's it folks,
Thanks for visiting and see you next time!

How to convert numbers to Roman numerals in SQL Server Romain Numeral

How to convert numbers to Roman numerals in SQL Server Romain Numeral