Convert Decimal to and from Roman Numerals
Applicability:
SQL Server 2000: Tested
SQL Server 2005: Tested
SQL Server 2008: Tested
SQL Server 2008R2: Tested
SQL Server 2012: Not Tested
Credits:
Author: Phil Factor (Red-Gate Software)
Date: 19 Sep 2011
Description
This is a must have for any serious DBA's collection :-)
Must have taken Phil Factor ages to get his head around this one.
These are 2 functions to do eatcly what the title says.
Code
Convert from Integer to Roman Numerals:
IF OBJECT_ID('dbo.ToRomanNumerals') is NOT NULL
DROP FUNCTION dbo.ToRomanNumerals
GO
CREATE FUNCTION dbo.ToRomanNumerals (@Number INT)
/**
summary: >
This is a simple routine for converting a decimal integer into a roman numeral.
Author: Phil Factor
Revision: 1.0
date: 19th Sept 2011
example:
- code: Select dbo.ToRomanNumerals(187)
- code: Select dbo.ToRomanNumerals(2011)
returns: >
The Mediaeval-style 'roman' numeral as a string.
**/
RETURNS NVARCHAR(100)
AS
BEGIN
DECLARE @RomanNumeral AS NVARCHAR(100)
DECLARE @RomanSystem TABLE (symbol NVARCHAR(20),
DecimalValue INT)
IF @Number<0
BEGIN
RETURN 'De romanorum non numero negative'
END
IF @Number> 200000
BEGIN
RETURN 'O Juppiter, magnus numerus'
END
INSERT INTO @RomanSystem (symbol, DecimalValue)
SELECT 'I' AS symbol, 1 AS DecimalValue
UNION ALL SELECT 'IV', 4
UNION ALL SELECT 'V', 5
UNION ALL SELECT 'IX', 9
UNION ALL SELECT 'X', 10
UNION ALL SELECT 'XL', 40
UNION ALL SELECT 'L', 50
UNION ALL SELECT 'XC', 90
UNION ALL SELECT 'C', 100
UNION ALL SELECT 'CD', 400
UNION ALL SELECT 'D', 500
UNION ALL SELECT 'CM', 900
UNION ALL SELECT 'M', 1000
UNION ALL SELECT N'|??', 5000
UNION ALL SELECT N'cc|??', 10000
UNION ALL SELECT N'|???', 50000
UNION ALL SELECT N'ccc|???', 100000
UNION ALL SELECT N'ccc|??????', 150000
WHILE @Number > 0
SELECT @RomanNumeral = COALESCE(@RomanNumeral, '') + symbol,
@Number = @Number - DecimalValue
FROM @RomanSystem
WHERE DecimalValue = (SELECT MAX(DecimalValue)
FROM @RomanSystem
WHERE DecimalValue <= @number)
RETURN COALESCE(@RomanNumeral,'nulla')
END
GO
/* and we do our unit tests. */
if NOT dbo.ToRomanNumerals(87) = 'LXXXVII'
RAISERROR ('failed first test',16,1)
if NOT dbo.ToRomanNumerals(99) = 'XCIX'
RAISERROR ('failed second test',16,1)
if NOT dbo.ToRomanNumerals(0) = 'nulla'
RAISERROR ('failed third test',16,1)
if NOT dbo.ToRomanNumerals(300000) = 'O Juppiter, magnus numerus'
RAISERROR ('failed fourth test',16,1)
if NOT dbo.ToRomanNumerals(2725) = 'MMDCCXXV'
RAISERROR ('failed fifth test',16,1)
if NOT dbo.ToRomanNumerals(949) = 'CMXLIX'
RAISERROR ('failed Sixth test',16,1)
GO
Convert from Roman Numerals to Integer:
TBODY>
F OBJECT_ID('dbo.FromRomanNumerals') is NOT NULL
DROP FUNCTION dbo.FromRomanNumerals
GO
CREATE FUNCTION dbo.FromRomanNumerals (@RomanNumeral NVARCHAR(100))
/**
summary: >
This is a simple routine for converting roman numeral into an integer
Author: Phil Factor
Revision: 1.0
date: 20th Sept 2011
example:
- code: Select dbo.FromRomanNumerals('CXVII')
- code: Select dbo.FromRomanNumerals('')
returns: >
The Integer.
**/
RETURNS int
AS
BEGIN
DECLARE @RomanSystem TABLE (symbol NVARCHAR(20),
DecimalValue INT)
DECLARE @Numeral INT
DECLARE @Rowcount INT
DECLARE @InString INT
SELECT @inString=LEN(@RomanNumeral),@rowcount=100
IF @RomanNumeral='nulla' RETURN 0
INSERT INTO @RomanSystem (symbol, DecimalValue)
SELECT 'I' AS symbol, 1 AS DecimalValue
UNION ALL SELECT 'IV', 4
UNION ALL SELECT 'V', 5
UNION ALL SELECT 'IX', 9
UNION ALL SELECT 'X', 10
UNION ALL SELECT 'XL', 40
UNION ALL SELECT 'L', 50
UNION ALL SELECT 'XC', 90
UNION ALL SELECT 'C', 100
UNION ALL SELECT 'CD', 400
UNION ALL SELECT 'D', 500
UNION ALL SELECT 'CM', 900
UNION ALL SELECT 'M', 1000
UNION ALL SELECT N'|??', 5000
UNION ALL SELECT N'cc|??', 10000
UNION ALL SELECT N'|???', 50000
UNION ALL SELECT N'ccc|???', 100000
UNION ALL SELECT N'ccc|??????', 150000
WHILE @instring>0 AND @RowCount>0
BEGIN
SELECT TOP 1 @Numeral=COALESCE(@Numeral,0)+ DecimalValue,
@InString=@Instring-LEN(symbol)
FROM @RomanSystem
WHERE RIGHT(@RomanNumeral,@InString) LIKE symbol+'%'
COLLATE SQL_Latin1_General_CP850_Bin
AND @Instring-LEN(symbol)>=0
ORDER BY DecimalValue DESC
SELECT @Rowcount=@@ROWCOUNT
END
RETURN CASE WHEN @RowCount=0 THEN NULL ELSE @Numeral END
END
GO
/* and we do our unit tests. */
if NOT dbo.FromRomanNumerals ('LXXXVII')=87
RAISERROR ('failed first test',16,1)
if NOT dbo.FromRomanNumerals('XCIX') = 99
RAISERROR ('failed second test',16,1)
if NOT dbo.FromRomanNumerals('nulla') = 0
RAISERROR ('failed third test',16,1)
if NOT dbo.FromRomanNumerals('MMDCCXXV')= 2725
RAISERROR ('failed fourth test',16,1)
if NOT dbo.FromRomanNumerals('CMXLIX') = 949
RAISERROR ('failed fifth test',16,1)