Convert Decimal to and from 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

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)