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
