Cool Tips‎ > ‎Data Conversion‎ > ‎

Data Conversion - Convert Decimal to and from Roman Numerals

Applicability:

                 SQL Server 2000:        Tested
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Not Tested        

Credits:

Author:    Phil Factor  (Red-Gate Software)
Date:        19 Sep 2011

Description

This is a must have for any serious DBA's collection :-)
Must have taken Phil Factor ages to get his head around this one.
 
These are 2 functions to do eatcly what the title says. 

Code

Convert from Integer to  Roman Numerals:

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.0
date: 19th Sept 2011
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),
                              
DecimalValue INT)

  
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

 

 

 Convert from Roman Numerals to Integer:

TBODY>

F 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.0
date: 20th Sept 2011
example:
     - code: Select dbo.FromRomanNumerals('CXVII')
     - code: Select dbo.FromRomanNumerals('')
returns:   >
The Integer.
**/    
RETURNS int
AS
BEGIN
  DECLARE
@RomanSystem TABLE (symbol NVARCHAR(20),
                              
DecimalValue INT)
  
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)
  


ċ
Convert to and From ROMAN NUMERALS!.sql
(5k)
Andy Hughes,
Jun 23, 2012, 1:12 AM
Comments