Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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'


Comments

Posted by SQL Noob on 22 September 2011

thx

i was going to apply to the Vatican DBA job next week

Posted by Phil Factor on 22 September 2011

Si vos es opus in Vaticano ut offerret DBA, dicere de reminisci quam bonum sit amet SQL Server

Posted by jcrawf02 on 22 September 2011

Dear Phil-ibuster, please enlighten those of us peasants who have no idea why "[t]hey are handy for all sorts of little jobs"? To what use have you put this?

Posted by Phil Factor on 22 September 2011

It is in dealing with some seriously legacy data. The last time I used it was for taking a printed index for some data that I had to get into a database for a web application. The index was in roman numerals and I wanted it in decimal. Roman numerals are used quite a lot in numbered paragraphs, lists and so on  particularly with older documents, and it can be a pig to decypher them. With numerals, you sometimes need to differentiate them, the way we use bold, italic with words. The convention until recently used to be to use roman numerals for such things as sub-paragraphs, to differentiate them from paragraphs.

Posted by Phil Factor on 22 September 2011

...or for anyone else destined for the Vatican ......

In gerendis autem quidam senex valde notitia. Permaneo vicis EGO adsuesco assuesco quod pro aliqua data index Excudebat accipit mihi enim, ut tincidunt eros in velit. Ad indicem numerorum Romanorum, in decimales in volebam. Haud multum in usu numerorum Romanorum numerus sections of text tabulae maxime *** senioribus et cetera documenta, et potest, ut procum decypher eos. Per numeros, qui interdum ad differentiam eorum, modo utimur audax italic verbis. De conventu insquequo nuper numerorum Romanorum, uti solet ad illa quae minus paragraphs, ad differentiam a orci.

Posted by GilaMonster on 22 October 2011

May I use this in a blog post? It would be perfect for an example of how data is (not) sorted on a page.

Posted by Phil Factor on 24 October 2011

Of course you may, Gail. I'll look forward to hearing how it is perfect for an example of how data is (not) sorted on a page. Probabiliter tantum stultus ego miserum senem, sed me esse idea quid velis.

Posted by simon.whiteley 80174 on 31 December 2012

To allow it to cope with lowercase roman numerals:

ALTER FUNCTION [dbo].[FromRomanNumerals]

.....

SET @RomanNumeral = UPPER(@RomanNumeral)

Posted by daveeasa on 1 November 2013

NOTE:  LLC translates to 200 but should probably be rejected as invalid.  Ironically that was my reason for stopping by, I have to remove company name suffixes with roman numbers but I want to preserve LLC.

Leave a Comment

Please register or log in to leave a comment.