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

Phil Factor, 2011-09-20

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=@InstringLEN(symbol)

FROM

   

@RomanSystem

   

Where RIGHT(@RomanNumeral,@InString)

LIKE symbol+‘%’


          COLLATE SQL_Latin1_General_CP850_Bin
    
AND @InstringLEN(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

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis

2009-02-23

1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren

2009-02-17

1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren

2009-02-13

360 reads