{"id":6570,"date":"2014-02-03T16:46:42","date_gmt":"2014-02-03T16:46:42","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/converting-roman-numerals-more-tsql-program-chrestomathy\/"},"modified":"2016-07-07T14:24:04","modified_gmt":"2016-07-07T14:24:04","slug":"converting-roman-numerals-more-tsql-program-chrestomathy","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/converting-roman-numerals-more-tsql-program-chrestomathy\/","title":{"rendered":"Converting Roman Numerals: More TSQL Program Chrestomathy."},"content":{"rendered":"<p>As part of my series on TSQL Program Chrestomathy, this is a couple of TSQL routines that convert between integers and roman numerals.\u00a0 There are two functions, one for each direction of conversion. These are a slight revision and expansion of what I published on a blog in 2011. I&#8217;ve added a bit more of the original test suite The problem with Roman Numerals is that there is no ANSI Standard for them. Originally, it was a simple tally system which grew rather organically, and was still being developed up to the time (14th Century) that we sighed and adopted hindu-arabic numerals. Even after that point, they were being used for timber construction until the eighteenth century because the marks were easy to make with a chisel. Nowadays, they are used occasionally for floor numbering, in books and documents for paragraph numbering, in some countries for denoting the day of the week, for clock-faces,\u00a0 and\u00a0 The subtractive notation was a late arrival, though that and the double-subtractive were used in Roman times. To get some decent-sized numbers, I use the system based on Etruscan usage for large numbers, though I agree that it all goes a bit nebulous after 200000.<\/p>\n<pre class=\"theme:ssms2012-simple-talk lang:tsql decode:true \" >\r\n IF OBJECT_ID('dbo.ToRomanNumerals') is NOT NULL\r\n \u00a0\u00a0\u00a0\u00a0 drop function dbo.ToRomanNumerals\r\n go\r\n CREATE FUNCTION dbo.ToRomanNumerals (@Number INT)\r\n \u00a0\/**\r\n summary:\u00a0\u00a0 &gt;\r\n This is a simple routine for converting a decimal integer into a roman numeral.\r\n Author: Phil Factor\r\n Revision: 1.2\r\n date: 3rd Feb 2014\r\n Why: converted to run on SQL Server 2008-12\r\n example:\r\n \u00a0\u00a0\u00a0\u00a0 - code: Select dbo.ToRomanNumerals(187)\r\n \u00a0\u00a0\u00a0\u00a0 - code: Select dbo.ToRomanNumerals(2011)\r\n returns:\u00a0\u00a0 &gt;\r\n The Mediaeval-style 'roman' numeral as a string.\r\n **\/\u00a0\u00a0 \r\n RETURNS NVARCHAR(100)\r\n AS\r\n BEGIN\r\n \u00a0IF @Number&lt;0\r\n \u00a0\u00a0\u00a0 BEGIN\r\n \u00a0\u00a0\u00a0 RETURN 'De romanorum non numero negative'\r\n \u00a0\u00a0\u00a0 end\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n \u00a0 IF @Number&gt; 200000\r\n \u00a0\u00a0\u00a0 BEGIN\r\n \u00a0\u00a0\u00a0 RETURN 'O Juppiter, magnus numerus'\r\n \u00a0\u00a0\u00a0 end\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n \u00a0 DECLARE @RomanNumeral AS NVARCHAR(100)\r\n \u00a0 DECLARE @RomanSystem TABLE (symbol NVARCHAR(20) \r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 COLLATE SQL_Latin1_General_Cp437_BIN ,\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0DecimalValue INT PRIMARY key)\r\n \u00a0\u00a0 INSERT\u00a0 INTO @RomanSystem (symbol, DecimalValue)\r\n \u00a0\u00a0\u00a0 VALUES('I', 1),\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ('IV', 4),\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ('V', 5),\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ('IX', 9),\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ('X', 10),\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ('XL', 40),\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ('L', 50),\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ('XC', 90),\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ('C', 100),\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ('CD', 400),\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ('D', 500),\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ('CM', 900),\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ('M', 1000),\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (N'|\u2184\u2184', 5000),\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (N'cc|\u2184\u2184', 10000),\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (N'|\u2184\u2184\u2184', 50000),\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (N'ccc|\u2184\u2184\u2184', 100000),\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (N'ccc|\u2184\u2184\u2184\u2184\u2184\u2184', 150000)\r\n \u00a0\r\n \u00a0 WHILE @Number &gt; 0\r\n \u00a0\u00a0\u00a0 SELECT\u00a0 @RomanNumeral = COALESCE(@RomanNumeral, '') + symbol,\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @Number = @Number - DecimalValue\r\n \u00a0\u00a0\u00a0 FROM\u00a0\u00a0\u00a0 @RomanSystem\r\n \u00a0\u00a0\u00a0 WHERE\u00a0\u00a0 DecimalValue = (SELECT\u00a0 MAX(DecimalValue)\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0\u00a0\u00a0 @RomanSystem\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE\u00a0\u00a0 DecimalValue &lt;= @number)\r\n \u00a0 RETURN COALESCE(@RomanNumeral,'nulla')\r\n END\r\n go\r\n \u00a0\r\n \/* and we do our unit tests. *\/\r\n if NOT dbo.ToRomanNumerals(87) = 'LXXXVII'\r\n \u00a0 RAISERROR ('failed first test',16,1)\r\n if NOT dbo.ToRomanNumerals(99) = 'XCIX'\r\n \u00a0 RAISERROR ('failed second test',16,1) \r\n if NOT dbo.ToRomanNumerals(0) = 'nulla'\r\n \u00a0 RAISERROR ('failed third test',16,1)\u00a0\u00a0 \r\n if NOT dbo.ToRomanNumerals(300000) = 'O Juppiter, magnus numerus'\r\n \u00a0 RAISERROR ('failed fourth test',16,1)\u00a0\u00a0 \r\n if NOT dbo.ToRomanNumerals(2725) = 'MMDCCXXV'\r\n \u00a0 RAISERROR ('failed fifth test',16,1)\u00a0\u00a0 \r\n if NOT dbo.ToRomanNumerals(949) = 'CMXLIX'\r\n \u00a0 RAISERROR ('failed Sixth test',16,1)\u00a0\u00a0 \r\n if NOT dbo.ToRomanNumerals(154321) = N'ccc|\u2184\u2184\u2184\u2184\u2184\u2184MMMMCCCXXI'\r\n \u00a0 RAISERROR ('failed Seventh test',16,1)\u00a0\u00a0 \r\n GO\r\n \u00a0\r\n \u00a0\r\n IF OBJECT_ID('dbo.FromRomanNumerals') is NOT NULL\r\n \u00a0 drop function dbo.FromRomanNumerals\r\n go\r\n CREATE FUNCTION dbo.FromRomanNumerals (@RomanNumeral NVarchar(100))\r\n \u00a0\/**\r\n summary:\u00a0\u00a0 &gt;\r\n This is a simple routine for converting\u00a0 roman numeral into an integer\r\n Author: Phil Factor\r\n Revision: 1.2\r\n date: 2nd Feb 2014\r\n Why: converted to run on SQL Server 2008-12\r\n example:\r\n \u00a0\u00a0\u00a0\u00a0 - code: Select dbo.FromRomanNumerals('CXVII')\r\n \u00a0\u00a0\u00a0\u00a0 - code: Select dbo.FromRomanNumerals('')\r\n returns:\u00a0\u00a0 &gt;\r\n The Integer.\r\n **\/\u00a0\u00a0 \r\n RETURNS int\r\n AS\r\n BEGIN\r\n \u00a0 DECLARE @RomanSystem TABLE (symbol NVARCHAR(20)\u00a0 \r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 COLLATE SQL_Latin1_General_Cp437_BIN,\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 DecimalValue INT PRIMARY key)\r\n \u00a0 DECLARE @Numeral INT\r\n \u00a0 DECLARE @Rowcount int\r\n \u00a0 DECLARE @InString int\r\n \u00a0 SELECT\u00a0 @inString=LEN(@RomanNumeral),@rowcount=100\r\n IF @RomanNumeral='nulla' return 0\r\n \u00a0 INSERT\u00a0 INTO @RomanSystem (symbol, DecimalValue)\r\n \u00a0\u00a0\u00a0 VALUES('I', 1),\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ('IV', 4),\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ('V', 5),\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ('IX', 9),\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ('X', 10),\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ('XL', 40),\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ('L', 50),\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ('XC', 90),\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ('C', 100),\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ('CD', 400),\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ('D', 500),\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ('CM', 900),\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ('M', 1000),\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (N'|\u2184\u2184', 5000),\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (N'cc|\u2184\u2184', 10000),\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (N'|\u2184\u2184\u2184', 50000),\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (N'ccc|\u2184\u2184\u2184', 100000),\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (N'ccc|\u2184\u2184\u2184\u2184\u2184\u2184', 150000)\r\n \u00a0\r\n WHILE @instring&gt;0 AND @RowCount&gt;0\r\n \u00a0\u00a0\u00a0 BEGIN\r\n \u00a0\u00a0\u00a0 SELECT TOP 1 @Numeral=COALESCE(@Numeral,0)+ DecimalValue,\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @InString=@Instring-LEN(symbol) FROM\r\n \u00a0\u00a0\u00a0 @RomanSystem\u00a0 \r\n \u00a0\u00a0\u00a0 Where RIGHT(@RomanNumeral,@InString) LIKE symbol+'%'\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 COLLATE SQL_Latin1_General_CP850_Bin\r\n \u00a0\u00a0\u00a0 AND @Instring-LEN(symbol)&gt;=0\r\n \u00a0\u00a0\u00a0 ORDER BY DecimalValue DESC\r\n \u00a0\u00a0\u00a0 SELECT @Rowcount=@@Rowcount\r\n \u00a0\u00a0\u00a0 end\r\n \u00a0RETURN CASE WHEN @RowCount=0 THEN NULL ELSE @Numeral END\r\n END\r\n go\r\n \/* and we do our unit tests. *\/\r\n if NOT dbo.FromRomanNumerals ('LXXXVII')=87\r\n \u00a0 RAISERROR ('failed first test',16,1)\r\n if NOT dbo.FromRomanNumerals('XCIX') = 99\r\n \u00a0 RAISERROR ('failed second test',16,1) \r\n if NOT dbo.FromRomanNumerals('nulla') = 0\r\n \u00a0 RAISERROR ('failed third test',16,1)\u00a0\u00a0 \r\n if NOT dbo.FromRomanNumerals('MMDCCXXV')= 2725\r\n \u00a0 RAISERROR ('failed fourth test',16,1)\u00a0\u00a0 \r\n if NOT dbo.FromRomanNumerals('CMXLIX') = 949\r\n RAISERROR ('failed fifth test',16,1) \r\n \u00a0\r\n \u00a0\r\n DECLARE @Start DATETIME\r\n SELECT @Start=GETDATE()\r\n DECLARE @ii INT\r\n SELECT @ii=1\r\n WHILE @ii&lt;200000\r\n \u00a0BEGIN\r\n \u00a0IF dbo.FromRomanNumerals (dbo.ToRomanNumerals(@ii)) &lt;&gt; @ii\r\n \u00a0\u00a0 BEGIN\r\n \u00a0\u00a0 RAISERROR ('failed iteration test at %d test',16,1,@ii)\r\n \u00a0\u00a0 SELECT dbo.ToRomanNumerals(@ii)\r\n \u00a0\u00a0 SELECT dbo.FromRomanNumerals(dbo.ToRomanNumerals(@ii))\r\n \u00a0\u00a0 BREAK\r\n \u00a0\u00a0 end\r\n \u00a0SELECT @ii=@ii+1\u00a0 \r\n \u00a0end\u00a0\u00a0 \r\n \u00a0SELECT 'That took '\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 + CONVERT(VARCHAR(10),DATEDIFF(ms,@start,GETDATE())) \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 + ' Ms'\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>As part of my series on TSQL Program Chrestomathy, this is a couple of TSQL routines that convert between integers and roman numerals.\u00a0 There are two functions, one for each direction of conversion. These are a slight revision and expansion of what I published on a blog in 2011. I&#8217;ve added a bit more of&#8230;&hellip;<\/p>\n","protected":false},"author":154613,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[],"coauthors":[],"class_list":["post-6570","post","type-post","status-publish","format-standard","hentry","category-blogs"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/6570","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/154613"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=6570"}],"version-history":[{"count":3,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/6570\/revisions"}],"predecessor-version":[{"id":22917,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/6570\/revisions\/22917"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=6570"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=6570"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=6570"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=6570"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}