http://www.sqlservercentral.com/blogs/philfactor/2011/09/20/roman-numerals-to-integers-and-vice-versa-in-sql/

Printed 2014/07/25 03:16PM

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

By 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=@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'



Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.