Blog Post

Roman Numerals to integers, and Vice Versa, in SQL:

,

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'

Rate

Share

Share

Rate