Saturday, August 10, 2013

Integer Value to Roman Number

Integer Value to Roman Number

From my collection here is a small function to convert the Integer value to roman number.  I want to share it with all my friends and readers.
Hope you like it.

/*
   This function converts the Integer numbers into Roman numbers.
   The max limit is 9999
  
   Testing
   ------------------------------------
   SELECT dbo.fnCONVTO_ROMAN(111)
   ------------------------------------
*/

IF OBJECT_ID(N'[dbo].[fnCONVTO_ROMAN]', N'FN') IS NOT NULL
   BEGIN
     DROP FUNCTION [dbo].[fnCONVTO_ROMAN];
   END
GO  
CREATE FUNCTION [dbo].[fnCONVTO_ROMAN]
      (
            @p_Number INT
      ) RETURNS VARCHAR(30)
AS
BEGIN
  IF @p_Number > 10000 RETURN '*'
  DECLARE @v_RomnNumber VARCHAR(30);
  SET @v_RomnNumber = REPLICATE('M',@p_Number/1000);
  SET @p_Number = @p_Number%1000;
  SET @v_RomnNumber = @v_RomnNumber + REPLICATE('C',@p_Number/100);
  SET @p_Number = @p_Number%100;
  SET @v_RomnNumber = @v_RomnNumber + REPLICATE('X',@p_Number/10);
  SET @p_Number = @p_Number%10;
  SET @v_RomnNumber = @v_RomnNumber + REPLICATE('I',@p_Number);

  SET @v_RomnNumber = REPLACE(@v_RomnNumber,REPLICATE('C',9),'CM');
  SET @v_RomnNumber = REPLACE(@v_RomnNumber,REPLICATE('X',9),'XC');
  SET @v_RomnNumber = REPLACE(@v_RomnNumber,REPLICATE('I',9),'IX');

  SET @v_RomnNumber = REPLACE(@v_RomnNumber,REPLICATE('C',5),'D');
  SET @v_RomnNumber = REPLACE(@v_RomnNumber,REPLICATE('X',5),'L');
  SET @v_RomnNumber = REPLACE(@v_RomnNumber,REPLICATE('I',5),'V');

  SET @v_RomnNumber = REPLACE(@v_RomnNumber,REPLICATE('C',4),'CD');
  SET @v_RomnNumber = REPLACE(@v_RomnNumber,REPLICATE('X',4),'XL');
  SET @v_RomnNumber = REPLACE(@v_RomnNumber,REPLICATE('I',4),'IV');

  RETURN @v_RomnNumber;
END
GO