Here are a couple of functions to convert between roman numerals and integers. They are handy for all sorts of little jobs, I've had them in some form or another for some time but dusted them out when someone was trying to tell me how compact some damn new OO language was and used a conversion routine as an example. SQL was capable of a more compact routine. This is used sometimes for interview questions so stick these in your back pocket. Natuarally, you'd choose to have the roman to integer conversion table as a view rather than construct it in a function. I know you can do these routines as CLRs but so what?
If you want the traditional style of Roman Numeral where 4 is written IIII instead of IV and forty is written XXXX instead of XL and so on, then just add the entries and it should all work fine. I thought the mediaeval style that you see on clock-faces was more of a challenge and anyway, it is standard in typography. It will do
Egyptian and Babylonian numerals too but I felt that this was getting to be minority interest.
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.1
date: 23rd Oct 2011
Why: Added explicit collation and added test to 200,000
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)
COLLATE SQL_Latin1_General_CP850_BIN ,
DecimalValue INT PRIMARY KEY)
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
IF
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.1
date: 23rd Oct 2011
Why: Added explicit collation and added test to 200,000
example:
- code: Select dbo.FromRomanNumerals('CXVII')
- code: Select dbo.FromRomanNumerals('')
returns: >
The Integer.
**/
RETURNS INT
AS
BEGIN
DECLARE @RomanSystem TABLE (symbol NVARCHAR(20)
COLLATE SQL_Latin1_General_CP850_BIN,
DecimalValue INT PRIMARY KEY)
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)
DECLARE @Start DATETIME
SELECT @Start=GETDATE()
DECLARE @ii INT
SELECT @ii=1
WHILE @ii<200000
BEGIN
IF dbo.FromRomanNumerals (dbo.ToRomanNumerals(@ii)) <> @ii
BEGIN
RAISERROR ('failed iteration test at %d test',16,1,@ii)
SELECT dbo.ToRomanNumerals(@ii)
SELECT dbo.FromRomanNumerals(dbo.ToRomanNumerals(@ii))
BREAK
END
SELECT @ii=@ii+1
END
SELECT 'That took '
+ CONVERT(VARCHAR(10),DATEDIFF(ms,@start,GETDATE()))
+ ' Ms'
