Click here to monitor SSC
  • Av rating:
  • Total votes: 58
  • Total comments: 18
Alex Kozak

Numeral Systems and Numbers Conversion in SQL

10 December 2007

Numeral systems can be fascinating. In everyday programming, we are now becoming quite insulated from the need to convert between binary numbers and their representation, so it is a novelty to try out ways of doing it in SQL, and experiment with other number systems from the past.

My fascination with numbers started when I was about two years old.

One of my parents used to bend my fingers, saying “One, Two, Three…” and all our guests smiled, when I  tried to repeat “Four, Five, Six…”

At that time, I believed that people need the numbers in order to count their fingers and to make their parents and relatives laugh.

When I grew up, I discovered that numbers could be helpful in a few more situations, for example to identify time and count money. However, I did not pay much attention to the fact that the counting rules for time and money are different.

In one day, among the old documents, stored in our family, I found my grandfather’s watch. The watch had a beautiful watch face with strange symbols on it, instead of numbers. My mother told me that these symbols are the Roman numbers.

That fact, however, did not make me curious about the Roman numbers. In my mind, they were associated with the old, broken watch – both, the numbers and the watch were not in use.

I got my first hints of other numeral system at university.

The computers were built of transistors – small electronic devices with two physical states: opened and closed. These states, being coded as 0 and 1, perfectly fitted into binary numeral system. Based on that system and supported by the powerful mathematical apparatus, the computers changed human life.

If you carefully look around, you will find many signs of different numeral systems. Some of them are not relevant anymore; the other ones we still use and develop. Time, and angles, for example, are still based on sexagesimal systems.

This article is about the numeral systems. It will also demonstrate interesting SQL techniques that can be used, when you need to convert the numbers from one numeral system to another.

A Brief History of Numeral Systems

The numbers and numeral systems are very old. The simple counting systems appeared when people started to develop speech, but even before, the first primitive people had notion of counting and could enumerate similar objects, using their fingers, pabbles, knotted roups or notches on sticks or bones.

When alphabets and written languages appeared, the simple counting systems transformed into quite complicated ways of writing numbers.

About 3000 - 3500 B.C,. number systems have developed spontaneously in different isolated cultures and so almost each civilization created its own language and numeral system.

There is no an official classification of the numeral systems. However, many researchers (especially a French mathematician Geneviève Guitel in her “Compared History of written numerations”, 1975) agree that written numeral systems should be divided into two main groups - additive and positional.

Georges Ifrah in his important work “The Universal History of Numbers: From Prehistory to the Invention of the Computer” gives more detailed classification of the numeral systems:

  • Additive systems
  • Hybrid systems
  • Positional systems

Let us take a closer look at these numeral systems.

1. Additive numeral systems

Additive numeral systems use principle of addition.

Any number in that system can be represented by the combination of symbols, where each symbol has its own value independent of position. The sum of the values of all the symbols gives the value of whole number.

There are three types of additive numeral systems.

The additive systems of Type 1 have separate symbol for 1 and for each power of 10 (or 20). The example of such a system would be Egyptian hieroglyphic system (see Fig.1):

Fig.1 Egyptian Hieroglyphic System (Additive numeral systems of Type 1)

Example:

The additive systems of Type 2 have special symbols for 1, 10, 100, 1000…. In addition, they have special symbols for 5, 50, 500…. The Roman numerals belong to additive systems of Type 2 (see Fig.2):

The additive systems of Type 3 use alphabets to represent the numbers.

The example of such a system would be Greek alphabetic number system (see Fig.3):

Fig.3 Greek Alphabetic Numeral System (Additive numeral systems of Type 3)

In alphabetic numeral system, each letter has a numeric value.

Then, any number can be represented by the set of letters, using the additive principle.

For instance,

12 = ?ß

543 = fµ?

There is special notation for the numbers greater than 999.

Note that alphabetic numeral systems could appear only when the alphabets and the writing systems came to the scene. Before that, many civilizations, including Greeks had other numeral systems, mostly the additive systems of Type 1 or 2.

2. Hybrid numeral systems

The hybrid numeral systems are the systems that use the principles of addition and multiplication.

There are few types of hybrid numeral systems. The example is a common Chinese number system (see Fig.4):

Fig.4 Common Chinese Numeral System (Hybrid numeral system)

Common Chinese numeral system has symbols for the numbers from 1 to 9 and for 10, 100, 1000 and 10000. It didn’t have a zero, but then later the symbol for zero was added.

3. Positional numeral systems

The difficulties of ancient additive and hybrid numeral systems are obvious:

- They didn’t have clear intuitive notation for the numbers.

- Representation of the big numbers usually required a lot of space

- Demand for the numbers greater than already existed in the system, required development of the new symbols or conventions.

- The additive and hybrid numeral systems were mostly static. Their number-symbols looked more like abbreviations and could appear only in the group of similar symbols or(and) in a certain position. That made the ancient numeral systems unsuitable for written arithmetic and requested auxiliary tools, like abacus, special tables or system-specific rules to do the calculations.

Therefore, the invention of positional numeral system was very important step for the human civilization.

In the positional system, the value of each symbol depends on position of that symbol in the number representation.

Except of our modern positional system, there were three positional systems in the history of civilization. They are systems of Babylonian scholars, Chinese scholars and Mayan astronomers.

The Babylonians used sexagesimal (base-60) positional numeral system, written in cuneiform (see Fig.5):

Fig.5 Babylonian sexagesimal positional numeral system

The first positional systems had one significant drawback. They were not really dynamic.

Indeed, in order to be dynamic the positional system needs to have only one symbol (from available in that system) in each position. In Babylonian system, a digit in each position could be composed of a few repeated cuneiform symbols (in Chinese and Mayan numeral systems a digit could be composed of a few bars or points).

May be because of that drawback they couldn’t compete with the more advanced Hindu-Arabic positional numeral system.

Our modern decimal system, also called “Arabic” or “Hindu-Arabic”, has been invented in India; developed by Arabic mathematicians and then came to Europe.)

Today, the decimal system is widely accepted. It became an official numeral system in practically all countries.

Three more positional systems – binary, octal and hexadecimal became very important in the last 40-50 years with the appearance of computers and information technology.

The positional numeral systems have following main characteristics:

- Can represent practically any number

- Truly dynamic (have one distinct number-sign in each position)

- Have special symbol zero that indicates the absence of the value (empty value) in the specific position

- The most suitable for the mathematical calculations among all numeral systems

- Provide the highest level of abstraction

Numbers’ Conversion in Positional Numeral Systems

The positional systems have base (radix).

The base is the amount of unique symbols that positional numeral system uses for the numbers’ representation. For instance, base-10 (decimal) numeral system has ten symbols 0,1,2 ,3,4,5,6,7,8,9,10; base-2 (binary) numeral system has two symbols 0,1.

It is obvious that numeral systems with the higher base provide more compact notation for the numbers.

Indeed, the same number 25510 = 111111112 = FF16 will occupy 3 positions in decimal numeral system, 8 positions in binary numeral system and 2 positions in hexadecimal numeral system.

However, it doesn’t mean that we need to switch to base-100 or base-1000 numeral system. It is much easier for us to recognize and operate with 10 distinct symbols (may be because human being has 10 fingers), than with 100 or 1000.

At the same time for the computers, it is easier to operate with two figures, even though it may produce a long sequence of binary symbols for the very large numbers.

Theoretically, the amount of the positional numeral systems can be unlimited.

In practice, there is a few dozens of different positional numeral systems and often we need to convert the numbers from one system to another.

In this paragraph, we will discuss SQL techniques that can be used for the numbers conversion.

Let us start with the number conversion from base-n numeral system to base-10 numeral system, where radix n can be any radix different than 10.

As an example, let us convert the binary numbers into decimal ones.

First, create and load an auxiliary table that will store the sequence of whole numbers (see Listing1):

Listing1. Create and load an auxiliary table

SET NOCOUNT ON;
DECLARE @n INT;
SET @n 100000;

IF EXISTS(SELECT FROM sysobjects 
   
WHERE ID (OBJECT_ID('sequence')) AND xtype 'U'
DROP TABLE sequence;
CREATE TABLE sequence(num bigint NOT NULL PRIMARY KEY);

WITH numbers AS
(
 
SELECT AS num
 
UNION ALL
 
SELECT num FROM numbers WHERE num @n
)
INSERT INTO sequence
SELECT num FROM numbers 
OPTION (MAXRECURSION 0);

Technique 1

Create and load test table (see Listing2):

Listing2. Create and load test table

SET NOCOUNT ON;
IF EXISTS(SELECT FROM sysobjects 
   
WHERE ID (OBJECT_ID('baseN_Values')) AND xtype 'U'
DROP TABLE baseN_Values;

CREATE TABLE baseN_Values (baseN_Val VARCHAR(100));

INSERT INTO baseN_Values VALUES ('10101');
INSERT INTO baseN_Values VALUES ('101010');
INSERT INTO baseN_Values VALUES ('10101000000000000001');
INSERT INTO baseN_Values VALUES ('101');
INSERT INTO baseN_Values VALUES ('1010');
INSERT INTO baseN_Values VALUES ('1010101');
INSERT INTO baseN_Values VALUES ('111111111');
INSERT INTO baseN_Values VALUES ('101011000100010001000100010010');
INSERT INTO baseN_Values VALUES ('1010101111111111');
INSERT INTO baseN_Values VALUES('10101011111111111111111111111111111111111111111110');
INSERT INTO baseN_Values VALUES ('101010000000000000000000011');
INSERT INTO baseN_Values VALUES ('10101001010101010101');
INSERT INTO baseN_Values VALUES ('101010010101010101011111000');
INSERT INTO baseN_Values VALUES ('1010101000');
INSERT INTO baseN_Values VALUES ('101010000000000');
INSERT INTO baseN_Values VALUES ('10000000101010');
INSERT INTO baseN_Values 
VALUES ('1000000000100000000000100000000000');

Now, you need only one SELECT statement to convert the numbers (see Listing3):

Listing3. Convert numbers from base-n numeral system to decimal one

DECLARE @base bigint;
SET @base 2;

SELECT t1.baseN_Val
(
SELECT SUM(SUBSTRINGt2.baseN_ValLEN(t2.baseN_Val)-num 11)* POWER(@basenum -1))
       
FROM sequence CROSS JOIN baseN_Values t2 
       
WHERE t1.baseN_Val t2.baseN_Val 
       
AND num <= LEN(t1.baseN_Val)) dec_Val
FROM baseN_Values t1;

Results:

baseN_Val                                             dec_Val
--------------------------------------------------    ---------------
10101                                                 21
101010                                                42
10101000000000000001                                  688129
101                                                   5
1010                                                  10
1010101                                               85
111111111                                             511
101011000100010001000100010010                        722538770
1010101111111111                                      44031
10101011111111111111111111111111111111111111111110    756463999909886
101010000000000000000000011                           88080387
10101001010101010101                                  693589
101010010101010101011111000                           88779512
1010101000                                            680
101010000000000                                       21504
10000000101010                                        8234
1000000000100000000000100000000000                    8598325248

In fact, SELECT statement from the Listing3 is a SQL notation of well-known formula that represents a real number X in the positional base-r numeral system:

X = cnRn + cn – 1Rn - 1 +…+ ciRi +... + c1R1 + c0R0 + c-1R-1 + ... + c-(n-1)R-(n-1) + c-nR-n

, where coefficient-multiplier ci can be any symbol from the base-r numbers’ set and R0, R1, … Ri are the radix in the power of the position-number minus 1.

Pay attention to data type of variable @base. That data type is bigint and defines data type of the result of the expression POWER(@base, num -1).

It is sufficient to have bigint in order to convert a 50-digits binary number

101010111111111111111111111111111111111111111111102

into decimal number

75646399990988610.

However, if you set variable @base to 8, assuming that table baseN_Values stores octal values, the script from the Listing3 will give you an error: Arithmetic overflow error converting expression to data type bigint.

That means bigint data type doesn’t have enough capacity to convert 50-digits octal number into decimal one. In order to convert a long octal number, you will need to make a change in the Listing3: you need to replace bigint data type of variable @base by float data type.

Using script from the Listing3, you can convert the numbers from any positional numeral system to decimal one.

Technique 2

Another approach to the numbers’ conversion is to build tables that specify the correspondence between the numbers in different numeral systems.

This approach can be easily implemented in SQL, using the following idea:

  1. Load n unique symbols of the base-n numeral system into the table
  2. Join that table to itself as many times, as the maximum number of digits you want to get.

For example, if you want to get 6-digits binary numbers, you can do this (see Listing4):

Listing4. Build sequence of 6-digits binary numbers

IF EXISTS(SELECT FROM sysobjects 
   
WHERE ID (OBJECT_ID('Bin')) AND xtype 'U'
DROP TABLE Bin;

CREATE TABLE bin(binNum VARCHAR(100));
INSERT INTO bin VALUES('0');
INSERT INTO bin VALUES('1');

SELECT (b1.binNum b2.binNum b3.binNum 
        
b4.binNum b5.binNum b6.binNumAS binValue
   
FROM bin b1 CROSS JOIN bin b2 CROSS JOIN bin b3 
        
CROSS JOIN bin b4 CROSS JOIN bin b5 CROSS JOIN bin b6
   
ORDER  BY 1
Results:

binValue
-----------
000000
000001
000010
000011
000100
000101
000110
000111
001000
001001
. . . .
111101
111110
111111

(64 row(s) affected)

The query from the Listing4, however, is very inconvenient. The problem is in its static nature.

Indeed, for 6-digits binary numbers you need 6 joins and 6 addends in the SELECT list. For 10-digits binary numbers you would need 10 joins and 10 addends in the SELECT list.

Each time when you need a specific length of the numbers, you need to rewrite the query.

You can solve that problem using dynamic SQL, but in SQL Server 2005 you can do something more elegant (see Listing5):

Listing5. Build sequence of 10-digits binary numbers using CTE

IF EXISTS(SELECT FROM sysobjects 
   
WHERE ID (OBJECT_ID('Bin')) AND xtype 'U'
DROP TABLE Bin;

CREATE TABLE bin(binNum VARCHAR(100));
INSERT INTO bin VALUES('0');
INSERT INTO bin VALUES('1');

DECLARE @lenght INT;
SELECT @lenght 10;

WITH numbers AS 

SELECT CAST(binNum AS VARCHAR(100)) AS base2 FROM bin
UNION ALL 
SELECT CAST((t2.binNum base2AS VARCHAR(100)) 
   
FROM numbers CROSS JOIN bin t2 
   
WHERE LEN(base2) < @lenght 

SELECT ROW_NUMBER() OVER (ORDER BY base2) - AS base10base2   
   
FROM numbers 
   
WHERE LEN(base2) > @lenght 1
OPTION (MAXRECURSION 0); 
Results:

base10               base2
------               ----------
0                    0000000000
1                    0000000001
2                    0000000010
3                    0000000011
4                    0000000100
5                    0000000101
. . . . . . . . . . . . . . . .
1018                 1111111010
1019                 1111111011
1020                 1111111100
1021                 1111111101
1022                 1111111110
1023                 1111111111

(1024 row(s) affected)

This solution will change the query dynamically, but it works only for binary numbers.

If you want your solution to work with any base (well, almost any), you need to use the approach, shown in the Listing6:

Listing6. Almost universal solution with user-defined function

IF OBJECT_ID (N'dbo.udf_GetNumbers'N'TF'IS NOT NULL
    
DROP FUNCTION dbo.udf_GetNumbers;
GO

CREATE FUNCTION dbo.udf_GetNumbers (@base INT@lenght INT)
RETURNS @baseN_numbers TABLE 
(
    
decNum INT PRIMARY KEY NOT NULL,
    
baseN_Num VARCHAR(50) NOT NULL
)
AS
BEGIN
   WITH 
tblBase AS 
   

   
SELECT CAST(AS VARCHAR(50)) AS baseNum
    
UNION ALL
    
SELECT CAST((baseNum 1AS VARCHAR(50)) 
          
FROM tblBase WHERE baseNum @base-1
   
),
   
numbers AS 
   
(
   
SELECT CAST(baseNum AS VARCHAR(50)) AS num FROM tblBase
   
UNION ALL 
SELECT CAST((t2.baseNum numAS VARCHAR(50)) FROM numbers CROSS JOIN tblBase t2 
      
WHERE LEN(NUM) < @lenght 
   

   
INSERT INTO @baseN_numbers
   
SELECT ROW_NUMBER() OVER (ORDER BY NUM) -AS rowIDNUM 
       
FROM numbers WHERE LEN(NUM) > @lenght 1
   
OPTION (MAXRECURSION 0); 
   
RETURN
END

From here, everything becomes easy. If you need, for example to build a table with decimal and corresponding binary, ternary and octal numbers, you can run the next query (see Listing7):

Listing7. Example with decimal, binary, ternary and octal numbers

SELECT U1.decNum base10
       
U1.baseN_Num base2
       
U2.baseN_Num base3,
       
U3.baseN_Num base8 
FROM dbo.udf_GetNumbers(210U1 
INNER JOIN dbo.udf_GetNumbers(37U2 ON u1.decNum u2.decNum
INNER JOIN dbo.udf_GetNumbers(84U3 ON u2.decNum u3.decNum
Results:

base10   base2          base3       base8
------   ----------     -------     -----
0        0000000000     0000000     0000
1        0000000001     0000001     0001
2        0000000010     0000002     0002
3        0000000011     0000010     0003
4        0000000100     0000011     0004
5        0000000101     0000012     0005
6        0000000110     0000020     0006
7        0000000111     0000021     0007
8        0000001000     0000022     0010
. . . . . . . . . . . . . . . . . . . .
1016     1111111000     1101122     1770
1017     1111111001     1101200     1771
1018     1111111010     1101201     1772
1019     1111111011     1101202     1773
1020     1111111100     1101210     1774
1021     1111111101     1101211     1775
1022     1111111110     1101212     1776
1023     1111111111     1101220     1777

 

As I mentioned earlier, the solution from the Listing6 is almost universal. It will work fine for all the bases lower than 10.

However, for any numeral system, that has special symbols (like A and B in duodecimal numeral system) you will need explicitly load table with all the symbols of that base-n system and then run the query from the Listing5.

Conversion from Decimal to Roman Numbers in SQL

Roman numeral system (adapted from Etruscans numeral system) was in use in ancient Rome and Roman Empire for more than thousand years.

That system was the main numeral system in Europe until 14th century and then gradually, during the centuries was supplanted by the Hindu-Arabic decimals.

Today, Roman numerals are still in use. They can be found in clocks and calendars; in books and media; in art, antique shops and museums; in chemistry; as the list numbers (you can enumerate pages of the Word document, using the roman numerals) and more.

As we saw earlier, the Roman numerals belong to additive numeral systems (see Fig2). Decimal numbers in turn represent positional numeral systems.

Both systems are governed by different rules and that makes the numbers’ conversion between Roman and decimal systems quite tricky.

However, there is a simple way to do such a conversion in SQL.

First, create auxiliary tables with Roman numbers (see Listing8):

Listing8. Create auxiliary tables with Roman numbers

/* Basic symbols of Roman numerals

I  V   X   L   C   D   M
1  5   10  50  100 500 1000

------------------------------------------*/

IF EXISTS(SELECT FROM sysobjects 
   
WHERE ID (OBJECT_ID('dec_rom1_9')) AND xtype 'U'
DROP TABLE dec_rom1_9;
GO
SELECT t1.decNumt1.romaNum INTO dec_rom1_9
FROM 
(SELECT AS decNum'' AS romaNum 
UNION ALL 
SELECT 1'I'
UNION ALL
SELECT 2'II'
UNION ALL
SELECT 3'III'
UNION ALL
SELECT 4'IV'
UNION ALL
SELECT 5'V'
UNION ALL
SELECT 6'VI'
UNION ALL
SELECT 7'VII'
UNION ALL
SELECT 8'VIII'
UNION ALL
SELECT 9'IX'
t1
--------------------------------------------------------

IF EXISTS(SELECT FROM sysobjects 
   
WHERE ID (OBJECT_ID('dec_romX_XC')) AND xtype 'U'
DROP TABLE dec_romX_XC;
GO
SELECT t1.decNumt1.romaNum INTO dec_romX_XC
FROM 
(SELECT AS decNum'' AS romaNum 
UNION ALL
SELECT 10'X' 
UNION ALL
SELECT 20'XX'
UNION ALL
SELECT 30'XXX'
UNION ALL
SELECT 40'XL'
UNION ALL
SELECT 90'XC'
t1
--------------------------------------------------------

IF EXISTS(SELECT FROM sysobjects 
   
WHERE ID (OBJECT_ID('dec_romC_CM')) AND xtype 'U'
DROP TABLE dec_romC_CM;
GO
SELECT t1.decNumt1.romaNum INTO dec_romC_CM
FROM 
(SELECT 0  AS decNum'' AS romaNum 
UNION ALL
SELECT 100'C'
UNION ALL
SELECT 200'CC'
UNION ALL
SELECT 300'CCC'
UNION ALL
SELECT 400'CD'
UNION ALL
SELECT 900'CM'
t1

IF EXISTS(SELECT FROM sysobjects 
   
WHERE ID (OBJECT_ID('dec_romM')) AND xtype 'U'
DROP TABLE dec_romM;
GO
SELECT t1.decNumt1.romaNum INTO dec_romM
FROM
(SELECT 0  AS decNum'' AS romaNum 
UNION ALL
SELECT 1000'M'
UNION ALL
SELECT 2000'MM'
UNION ALL
SELECT 3000'MMM't1

Now you can get the decimal numbers with the corresponding Roman numbers in one query (see Listing9):

Listing9. Get decimal and corresponding Roman numbers

SELECT (t1.decNum t2.decNumAS decNum
       (
t1.romaNum t2.romaNumAS romaNum
FROM dec_romM t1 CROSS JOIN 
(SELECT (t1.decNum t2.decNumAS decNum,
        (
t1.romaNum t2.romaNumAS romaNum
FROM (SELECT AS decNum'' AS romaNum 
UNION ALL SELECT 500'D't1 
CROSS JOIN 
(SELECT (t1.decNum t2.decNumAS decNum,
        (
t1.romaNum t2.romaNumAS romaNum
FROM dec_romC_CM t1 CROSS JOIN 
(SELECT (t1.decNum t2.decNumAS decNum
        (
t1.romaNum t2.romaNumAS romaNum 
FROM (SELECT AS decNum'' AS romaNum 
UNION ALL SELECT 50'L't1 
CROSS JOIN 
(SELECT (t1.decNum t2.decNumAS decNum
        (
t1.romaNum t2.romaNumAS romaNum
FROM dec_romX_XC t1 CROSS JOIN dec_rom1_9 t2t2
WHERE NOT (t1.romaNum 'L' AND t2.romaNum LIKE ('%[LC]%'))) t2t2
WHERE NOT (t1.romaNum 'D' AND t2.romaNum LIKE ('%[DM]%'))) t2
ORDER BY 
Results:

decNum      romaNum
0    
1           I
2           II
3           III
4           IV
5           V
6           VI
7           VII
8           VIII
9           IX
10          X
. . . . . . . . . . . . . .

1947        MCMXLVII
1948        MCMXLVIII
1949        MCMXLIX
1950        MCML
1951        MCMLI
1952        MCMLII
1953        MCMLIII
1954        MCMLIV
1955        MCMLV
1956        MCMLVI
. . . . . . . . . . . . . . . . . . . . . . . . . .

3989        MMMCMLXXXIX
3990        MMMCMXC
3991        MMMCMXCI
3992        MMMCMXCII
3993        MMMCMXCIII
3994        MMMCMXCIV
3995        MMMCMXCV
3996        MMMCMXCVI
3997        MMMCMXCVII
3998        MMMCMXCVIII
3999       
MMMCMXCIX

For the basic symbols equal or greater than 4000, the horizontal bar should be placed on top of that symbol. That horizontal bar indicates multiplication by 1000. However, ASCII doesn’t have the Roman symbols with horizontal bar on top. This is why the solution from the Listing9 shows the Roman numbers up to 4000 only.

Practically, you can convert the numbers from almost any numeral system to modern one, using SQL. However, to do that, you need to find the character set with the required basic symbols of that numeral system.

Historical Puzzle

It is widely accepted as an axiom that Hindu/Arabic numeral system is much better and more advanced than Roman numeral system.

The main arguments are:

  • Roman numerals don’t have zero.
  • Roman numbers occupy more space than decimal numbers.
  • The arithmetic calculations with Roman numbers are very complicated or even impossible.
  • Roman numbers cannot be used in abstract mathematics.

Indeed, Romans don’t have as many achievements in pure mathematics as ancient Greeks. They were more interested in practical mathematics in order to build the roads, bridges and other constructions. They developed a special abacus (tabula) to facilitate the mathematical calculations.

However, even though Hindu/Arabic numeral system was accepted in Islamic Spain in 9th century, it took another 600 – 700 years to make it dominant number system in whole Europe.

During those 600-700 years all the disputes between Abacists (devotees of Roman numeral system) and Algorists (supporters of decimal numeral system) ended with the same result – nobody could prove the advantage of their system.

There are a few explanations why Hindu/Arabic positional system finally won:

  • It was much more convenient and faster for merchants and bookkeepers to use decimal numbers.
  • Hindu/Arabic system was more suitable for printing.
  • The book “Liber Abaci”of Leonardo Fibonacci helped to spread decimal positional system in Europe.
  • Roman numeral system restricted progress of mathematics and so development of civilization.

However, even now some people wondering whether it is possible to do the arithmetic calculations with roman numerals without converting them to an Arabic/Hindu notation.

If you know such a method, I'd be fascinated to know how it was done, especially in TSQL.

The editor will give a prize for the best SQL program that makes possible the addition (subtraction) operations with Roman numerals without conversion.

Alex Kozak

Author profile:

Alex Kozak is a Senior DBA/Analyst working for SAP Canada. He has more than 15 years of database and programming experience. Microsoft has included some of his articles in the MSDN Library.

Search for other articles by Alex Kozak

Rate this article:   Avg rating: from a total of 58 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: ..just to show a few possibilities
Posted by: Phil Factor (view profile)
Posted on: Tuesday, December 11, 2007 at 9:27 AM
Message: --these are general utility tables that show the
--rules for roman numerals

--firstly, let's get the first seven (in fact
--they go up to a million, since an overline
--denotes 'multiply by M'
CREATE TABLE RomanNumerals
(
MyID INT IDENTITY(1, 1),
symbol VARCHAR(5)
)

INSERT INTO RomanNumerals (symbol) SELECT 'I'
INSERT INTO RomanNumerals (symbol) SELECT 'V'
INSERT INTO RomanNumerals (symbol) SELECT 'X'
INSERT INTO RomanNumerals (symbol) SELECT 'L'
INSERT INTO RomanNumerals (symbol) SELECT 'C'
INSERT INTO RomanNumerals (symbol) SELECT 'D'
INSERT INTO RomanNumerals (symbol) SELECT 'M'

--this tells you how many of each are equivalent
--to the next
CREATE TABLE Carry
(
MyID INT IDENTITY(1, 1),
what VARCHAR(5),
symbol VARCHAR(1)
)

INSERT INTO Carry (what,symbol) SELECT 'IIIII','V'
INSERT INTO Carry (what,symbol) SELECT 'VV','X'
INSERT INTO Carry (what,symbol) SELECT 'XXXXX','L'
INSERT INTO Carry (what,symbol) SELECT 'LL','C'
INSERT INTO Carry (what,symbol) SELECT 'CCCCC','D'
INSERT INTO Carry (what,symbol) SELECT 'DD','M'

--this tells you what subtractive notations are
--allowed
CREATE TABLE Subtractive
(
MyID INT IDENTITY(1, 1),
what VARCHAR(10),
toWhat VARCHAR(10)
)
INSERT INTO subtractive (what,toWhat) SELECT 'IV','IIII'
INSERT INTO subtractive (what,toWhat) SELECT 'IX','VIIII'
INSERT INTO subtractive (what,toWhat) SELECT 'XL','XXXX'
INSERT INTO subtractive (what,toWhat) SELECT 'XC','LXXXX'
INSERT INTO subtractive (what,toWhat) SELECT 'CM','DCCCC'

--and a simple numbers table just in case you haven't
--got one
CREATE TABLE numbers ( number INT )
INSERT INTO numbers SELECT 1
INSERT INTO numbers SELECT 2
INSERT INTO numbers SELECT 3
INSERT INTO numbers SELECT 4
INSERT INTO numbers SELECT 5
INSERT INTO numbers SELECT 6
INSERT INTO numbers SELECT 7
INSERT INTO numbers SELECT 8
INSERT INTO numbers SELECT 9
INSERT INTO numbers SELECT 10
INSERT INTO numbers SELECT 11
INSERT INTO numbers SELECT 12
INSERT INTO numbers SELECT 13
INSERT INTO numbers SELECT 14
INSERT INTO numbers SELECT 15
INSERT INTO numbers SELECT 16
INSERT INTO numbers SELECT 17
INSERT INTO numbers SELECT 18
INSERT INTO numbers SELECT 19
INSERT INTO numbers SELECT 20


IF OBJECT_ID (N'dbo.AddR') IS NOT NULL
DROP FUNCTION dbo.AddR
GO
--Roman addition
CREATE FUNCTION dbo.AddR
(
@LTerm Varchar(20),
@RTerm Varchar(20)
)
RETURNS Varchar(20)
WITH EXECUTE AS CALLER
AS -- place the body of the function here
BEGIN
declare @sum VARCHAR(20)
--check for non-roman-numerals
IF PATINDEX('%[^IVXLCDM ]%',@lterm+@Rterm)>0 RETURN null
SELECT @LTERM = REPLACE(@Lterm, what, ToWhat),
@Rterm=REPLACE(@Rterm,what, ToWhat)
FROM subtractive
SELECT @sum = COALESCE(@sum, '') + symbol
from ( SELECT Symbol,
MyID
from numbers
INNER JOIN romannumerals
ON SUBSTRING(@Lterm, number, 1)
= romannumerals.symbol
UNION ALL
SELECT symbol,
MyID
from numbers
INNER JOIN romannumerals
ON SUBSTRING(@rterm, number, 1)
= romannumerals.symbol
) f
ORDER BY f.MyID DESC
SELECT @sum = REPLACE(@sum, what, symbol)
FROM carry
return @sum
END
GO
--and a quick regression test
if dbo.AddR('MDCCCVI','M')<>'MMDCCCVI'
RAISERROR ('test 1 failed',16,1)
if dbo.AddR('MDCCCV1','M')IS NOT null
RAISERROR ('invalid char test failed',16,1)
if dbo.AddR('CVII','LVIIII')<>'CLXVI'
RAISERROR ('test 2 failed',16,1)
if dbo.AddR('CVI','LVIIIIIIIIIIIII')<>'CLXXIIII'
RAISERROR ('test 3 failed',16,1)
if dbo.AddR('XIV','VI')<>'XX'
RAISERROR ('subtractive interpretation failed',16,1)

Subject: a nice write up, but why use SQL?
Posted by: Alex Kuznetsov (view profile)
Posted on: Tuesday, December 11, 2007 at 3:13 PM
Message: Alex,

The information in the beginning of the article was very interesting, thanks! Yet I don't think SQL is the right language for such problems. For instance, I think that the problem with Roman numbers is frequently asked on Perl interviews, and the Perl solution is much much shorter ;).

Cheers,
AK

Subject: ..just to show a few possibilities
Posted by: Alex Kozak (view profile)
Posted on: Tuesday, December 11, 2007 at 5:56 PM
Message: Phill, try this:

SELECT dbo.AddR('MCMXLVII','CMXCIX')

, where MCMXLVII = 1947 and CMXCIX = 999

The correct answer should be MMCMXLVI (2946), your function brings MMDCCCCXXX

In fact, 64% of all possible sums (for addends from 1 to 2000) give incorrect result, when using your function.

Examples:

CLXI + XXXI = CLXXXXII, should be CXCII

CCCXXIX + MDCIV = MDCCCCXXXIII, should be MCMXXXIII

And so on.

However, even though it’s incorrect, I like your solution. I feel that you are going in the rigth direction.

Can you, please try to fix your solution?

P.S. Just to be sure that we are using the same notation.
The modern notation (since Middle Ages) does not allow the roman symbol to be repeated more than three times in succession. For example, IIII or XXXX are wrong symbol combinations.
(However, the clock faces with Roman numerals often have IIII instead of IV, may be because it looks better.)

Regards,
Alex

Subject: a nice write up, but why use SQL?
Posted by: Alex Kozak (view profile)
Posted on: Tuesday, December 11, 2007 at 7:01 PM
Message: Here is a definition of numeral system from Encyclopedia Britannica:

"Any of various sets of symbols and the rules for using them to represent numbers, which are used to express how many objects are in a given set".

A little bit complicated, but the creator of that definition is clearly talking about the sets.

Since SQL is one of the best set-oriented languages, I thought it would be appropriate to use it for arithmetic operations with Roman numerals.

However, I am far from saying that SQL is the best programming language.
If addition operation (which should be relatively easy to implement) and subtraction (which is much more complicated) with Roman numerals can be done in Perl during the interview, I would like to know that solution.

Frankly, I don’t care what programming language would be used to solve this puzzle.
As soon as clear and efficient algorithm can be provided, the implementation in a specific language is not a problem (well, is almost not a problem).

Thanks for the comments.

Regards,
Alex

Subject: Errors, well, yes and no
Posted by: Phil Factor (view profile)
Posted on: Wednesday, December 12, 2007 at 5:22 AM
Message: The ...
declare @sum VARCHAR(20)
... should be ...
declare @sum VARCHAR(80)

The other 'error' is due simply to the fact that you are using the late mediaeval subtractive notation that was never used in antiquity. It is very simple to make the output produce subtractive notation as it actually does the initial conversion from it. However, my main interest is to understand how they did it then, rather than how one does it now for clock-faces and gravestones.

To produce subtractive roman numerals (which our ancestors would have scratched their heads at) add the lines ...
SELECT @sum = REPLACE(@sum, towhat, what)
FROM subtractive ORDER BY MyID Desc
...just before the ....
return @sum
Then one gets the same answers as you. I may have missed out an allowed subtractive in the initial table but then, it is just a matter of getting the rules in the initial table.

Now, what about soing a subtraction rather than an addition?


Subject: Re: a nice write up, but why use SQL?
Posted by: WebMister (view profile)
Posted on: Wednesday, December 12, 2007 at 5:52 AM
Message: Alex (K),
Sorry, but I think you're wrong. The Romans used Set-based operations with look-up tables for computation. So did the Babylonians. we even have fragments of clay 'pivot-tables' from 2000 BC, used as lookup tables for multiplication. Now, does that sound like Perl or SQL?
Select * from Tabula?

Subject: Re: Errors, well, yes and no
Posted by: Alex Kozak (view profile)
Posted on: Wednesday, December 12, 2007 at 10:46 PM
Message: 1) I don’t want to argue about the notation.
I used the modern subtractive notation. You used the older one.
There were also the variations that allow something like IIX for 8 or IC for 99.

2) For the notation that you used, the addition seems to be OK, though I don’t have time to check everything.

3) Your changes for subtractive notation still don’t work. However, I believe you can get it working.

4) If you really want to understand how Romans did the addition (subtraction) in their time, you need to imagine yourself in their time, let’s say 2000 years ago.
At that time, the positional decimal system did not exist for you, so you could not use decimal numbers at all (try the addition/subtraction now).

5) Yes, there was a question about the subtraction (see the very last sentence of the article).

Subject: Re: a nice write up, but why use SQL?
Posted by: Alex Kozak (view profile)
Posted on: Wednesday, December 12, 2007 at 11:46 PM
Message: Sounds like a dispute between Abacists and Algorists.

Extract the phrase "used Set-based operations with look-up tables" from your comments and you could think that somebody is talking about databases and SQL.

I agree that Romans had very good devices and methods for computation, but for theoretical (pure) mathematics, their numeral system seemed to be very heavy.

I found interesting fact in Internet: in the early European universities, the topic concerning the algorithms for multiplication and division using Roman numerals was a subject for a doctoral research.

Our dispute can be never-ending, but we should agree that Roman numerals (being in use as a dominant numeral system for approximately 2000 years) were replaced by more progressive (regressive?) positional decimal system.
Why?

Subject: Why place-value?
Posted by: Paul Murphy (not signed in)
Posted on: Tuesday, December 18, 2007 at 2:15 PM
Message: "...Our dispute can be never-ending, but we should agree that Roman numerals (being in use as a dominant numeral system for approximately 2000 years) were replaced by more progressive (regressive?) positional decimal system.
Why?..."

Try doing multiplication and then division with Roman numerals and I think you will agree that place-value is a lot easier.

Subject: Ooops
Posted by: Paul Murphy (not signed in)
Posted on: Tuesday, December 18, 2007 at 2:16 PM
Message: Sorry for the multiple posts guys.
Hopefully someone can clear them out. *blush*

Subject: Roman numeral math puzzle
Posted by: Joe Celko (not signed in)
Posted on: Tuesday, December 18, 2007 at 2:26 PM
Message: SQL is a table look up language, so build a table, as previously shown.

CREATE TABLE RomanNumerals
(i INTEGER NOT NULL PRIMARY KEY,
roman VARCHAR(10) NOT NULL);

CREATE PROCEDURE AddRoman (IN a VARCHAR(15), IN b VARCHAR(15), OUT c VARCHAR(15))
LANGUAGE SQL
DETERMINISTIC
SET c = (SELECT R3.roman
FROM RomanNumerals AS R3
WHERE R3.i = (SELECT R1.i + R2.i
FROM RomanNumerals AS R1,
RomanNumerals AS R2
WHERE R1.roman = a
AND R2.roman = b));

--without negative numbers, I did natural subtraction
CREATE PROCEDURE SubtractRoman (IN a VARCHAR(15), IN b VARCHAR(15), OUT c VARCHAR(15))
LANGUAGE SQL
DETERMINISTIC
SET c = (SELECT R3.roman
FROM RomanNumerals AS R3
WHERE R3.i = (SELECT ABS (R1.i - R2.i)
FROM RomanNumerals AS R1,
RomanNumerals AS R2
WHERE R1.roman = a
AND R2.roman = b));

Why make it computational?



Subject: Re: Roman numeral math puzzle
Posted by: Alex Kozak (view profile)
Posted on: Wednesday, December 19, 2007 at 12:52 AM
Message: Joe, you are right.

Using query from the Listing 9, you can load table RomanNumerals (in your solution) and then do the arithmetic calculations with Roman numbers.

The same approach can be used for any numeral system as soon as you have a lookup table for that system.

Your approach is simple and flexible. However, you do nothing with Roman numerals.
The queries in your solution replace Roman numbers with decimal ones, do the arithmetic calculations and then substitute decimal result with Roman number back.

Here is actually the catch.
Trying to understand how Romans did their calculations, we should forget about decimal numbers. But, we cannot forget them, because we were born in the world of decimal numbers and we use them always and everywhere.

I afraid the puzzle doesn’t have a solution in any programming language, because: implicitly or explicitly we will still use decimal numbers.

However, Romans did have some algorithms and lookup tables for arithmetic calculations.
Personally, I would be interested to know what algorithms they used and how their lookup tables were organized.

Subject: Lookup tables
Posted by: Phil Factor (view profile)
Posted on: Wednesday, December 19, 2007 at 2:28 AM
Message: Fortunately, there is a surviving lookup table from Babylonian times. It is just the top left-hand corner, but it tells you enough to see how they did it.
Roman multiplication needed a very small lookup table consisting of a 'pivot table' of the products of multiplying any symbol I V X D C L M and so on with any other. I'm not a mathematician but multiplication then becomes the sum of the products of (each symbol of the left term multiplied by each of the symbols in turn in the right term).

The Babylonian clay tablet worked stolidly from 1-n in both columns and rows, rather than assisting the set-based approach, but the mathematics used by commerce and that used in astronomy in Babylonian times was remarkably different. (This is a cautionary thought that is relevant to the study of the Archaeology of Computing)

Subject: Roman numeral calculations
Posted by: Anonymous (not signed in)
Posted on: Wednesday, December 19, 2007 at 3:34 PM
Message: >> Personally, I would be interested to know what algorithms they used and how their lookup tables were organized.<<

They used various forms of Abaci and their numerals were only for recording results. The look up tables were pretty much like what we use in Elementary school today, but tended to go to 12x12 or 20x20 rather than just 9x9 -- these were popular counts in traditional units (12 inches = 1 foot).

One form was a metal or wood board with grooves cut into it. You put pebbles ("calculi", hence Calculate, etc.) in the grooves; there was an area on the right for fractions based on 1/12.

http://en.wikipedia.org/wiki/Roman_abacus

This was later replaced with lines drawn on a counter top and metal tokens (jetons) for the pebbles. That is why we talk about "counters" as store furniture.

http://en.wikipedia.org/wiki/Jeton

The algorithms for addition and subtraction are pretty obvious. Multiplication and division are trickier, but basically you do it the same way you did it on paper. Or you use the Russian Peasant's algorithm, which is binary math in disguise

* Write each number at the head of a column.
* Double the number in the first column, and halve the number in the second column. If the number in the second column is odd, divide it by two and drop the remainder.
* If the number in the second column is even, cross out that entire row.
* Keep doubling, halving, and crossing out until the number in the second column is 1.
* Add up the remaining numbers in the first column. The total is the product of your original numbers.

Let's multiply 57 by 86 as an example:

Write each number at the head of a column.
57 86

Double the number in the first column, and halve the number in the second column.
57 86
114 43

If the number in the second column is even, cross out that entire row.

Keep doubling, halving, and crossing out until the number in the second column is 1.
57 86
114 43 *
228 21 *
456 10
912 5 *
1824 2
3648 1 *

Add up the remaining numbers in the first column and get 4902 (57 + 456 + 1824 + 3648).

http://mathforum.org/dr.math/faq/faq.peasant.html

Another approach which works well on a counting board is the Lattice method:

http://online.edfac.unimelb.edu.au/485129/wnproj/multiply/lattice.htm

I can do square and cube roots, but it is best to have a table of half-squares in teh Chinese method.

Subject: Roman numeral calculations
Posted by: Anonymous (not signed in)
Posted on: Wednesday, December 19, 2007 at 3:45 PM
Message: >> Personally, I would be interested to know what algorithms they used and how their lookup tables were organized.<<

They used various forms of Abaci and their numerals were only for recording results. The look up tables were pretty much like what we use in Elementary school today, but tended to go to 12x12 or 20x20 rather than just 9x9 -- these were popular counts in traditional units (12 inches = 1 foot).

One form was a metal or wood board with grooves cut into it. You put pebbles ("calculi", hence Calculate, etc.) in the grooves; there was an area on the right for fractions based on 1/12.

http://en.wikipedia.org/wiki/Roman_abacus

This was later replaced with lines drawn on a counter top and metal tokens (jetons) for the pebbles. That is why we talk about "counters" as store furniture.

http://en.wikipedia.org/wiki/Jeton

The algorithms for addition and subtraction are pretty obvious. Multiplication and division are trickier, but basically you do it the same way you did it on paper. Or you use the Russian Peasant's algorithm, which is binary math in disguise

* Write each number at the head of a column.
* Double the number in the first column, and halve the number in the second column. If the number in the second column is odd, divide it by two and drop the remainder.
* If the number in the second column is even, cross out that entire row.
* Keep doubling, halving, and crossing out until the number in the second column is 1.
* Add up the remaining numbers in the first column. The total is the product of your original numbers.

Let's multiply 57 by 86 as an example:

Write each number at the head of a column.
57 86

Double the number in the first column, and halve the number in the second column.
57 86
114 43

If the number in the second column is even, cross out that entire row.

Keep doubling, halving, and crossing out until the number in the second column is 1.
57 86
114 43 *
228 21 *
456 10
912 5 *
1824 2
3648 1 *

Add up the remaining numbers in the first column and get 4902 (57 + 456 + 1824 + 3648).

http://mathforum.org/dr.math/faq/faq.peasant.html

Another approach which works well on a counting board is the Lattice method:

http://online.edfac.unimelb.edu.au/485129/wnproj/multiply/lattice.htm

I can do square and cube roots, but it is best to have a table of half-squares in teh Chinese method.

Subject: crap
Posted by: Anonymous (not signed in)
Posted on: Thursday, August 07, 2008 at 4:55 PM
Message: a load of crap

Subject: Online date converter
Posted by: inscriptor (view profile)
Posted on: Friday, December 17, 2010 at 3:00 AM
Message: I've created the online webapp called "Creounity Time Machine: dating, history, written language" (http://apps.creounity.com/time_machine/en/), that covers as yet 17 chronological systems and works directly and vice versa (in reference to the Gregorian calendar).

All the converting stuff is implemented via JS.

Subject: Need to save chinese numerals in a column
Posted by: sumit.rastogi (view profile)
Posted on: Wednesday, July 11, 2012 at 5:34 AM
Message: Hi Alex,

I have a requirement to add a column in a table whose data type must be int/numeric/decimal and the data it would save must be in chinese numbers and want to do some manipulation on it.

I am able to save data in UTF8 String UDT(datatype) but this is basically characters rather than numbers(chinese), hence not able to do manipulation on it.

Please suggest any way to add a column as int/numeric/decimal which will save data as chinese numerals and do the mathematical manipulations on the column.

Thanks,
Sumit Rastogi

 

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Top Rated

SQL Server XML Questions You Were Too Shy To Ask
 Sometimes, XML seems a bewildering convention that offers solutions to problems that the average... Read more...

Continuous Delivery and the Database
 Continuous Delivery is fairly generally understood to be an effective way of tackling the problems of... Read more...

The SQL Server Sqlio Utility
 If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

The PoSh DBA - Reading and Filtering Errors
 DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.