Friday, December 29, 2017

How to create Roman Numerals function in SQL


CREATE Function [dbo].[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 

Result

No comments :

Post a Comment