Monday, November 1, 2010

Convert Decimal to ROMAN using T-SQL

Do you want to convert a number into Roman Equivalent using T-SQL code or function? If yes then here you go:

/**************************************************
CREATED BY : Hari Sharma
PURPOSE    : Convert Decimal to ROMAN Equivalent
HOW TO USE : SELECT dbo.GetRomanNo(18)
             --OUTPUT: XVIII
**************************************************/
CREATE Function GetRomanNo(@N as varchar(20))
RETURNS VARCHAR(100)
AS
BEGIN
  DECLARE @s varchar(100), @r varchar(100),
          @i bigint, @p int, @d bigint
  SET @s = ''
  SET @r = 'IVXLCDM' -- Roman Symbols

  /* There is no roman symbol for 0, but I don't
   want to return an empty string */
  IF @n=0
     SET @s = '0'
  ELSE
  BEGIN
     SELECT @p = 1, @i = ABS(@n)
     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 @n < 0
     SET @s = '-' + @s
   END


   RETURN @s
END
GO

Here are few FREE resources you may find helpful.