Views: 3.227 views
Hello everybody!
Good Morning!
In this post I will demonstrate how to convert a normal (base 10) number to a Roman numeral (X, XVII, etc.) using a Transact-SQL function in SQL Server that I found in the blog of Advaldo Paiva. Because it is a function, we can apply it for a simple conversion or for updating table data, for example.
Function Code:
| 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 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 | 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 |
Usage examples:
That's it guys,
Thanks for stopping by 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
