Representing numbers in the duodecimal system

Question

I would like to do some calculations in the duodecimal system in SQL Server. Is there an easy/or at least concise way to convert INTs represented in base-10 to duodecimal numbers?

Answer 1

Honestly, you are very likely better off doing to conversion in your application, not SQL Server. To store numerical values other than Decimal or Hexidecimal you will need to use a string based data type (varchar for example), however, this means a number of things:

1. The storage space for the value is significantly higher (at 1 or perhaps 2 bytes per character)
2. Ordering of values may be less than desireable ('10' is less than '2').

As such you are better off storing the decimal value in your database, and then taking those values and converting them into duodecimal in the application.

That being said, after these warnings, you can do this in T-SQL. The method I use here using GENERATE_SERIES to generate a a row for each power of 12, starting at 12^0 and up to 12^8 (meaning it'll handle up to 12^9-1). Then I use division and modulo operators to work out the value for each digit and then aggregate those values into your duodecimal value.

I pop this in a function, so that it can be easily called:

CREATE OR ALTER FUNCTION dbo.DecimalToDuodecimal (@I int) 
RETURNS TABLE 
AS RETURN

    SELECT CASE SIGN(@I) WHEN -1 THEN '-' ELSE '' END +
           STRING_AGG(DD.Duo,'') WITHIN GROUP (ORDER BY GS.value DESC) AS Duodecimal
    FROM GENERATE_SERIES(0,8) GS
         CROSS APPLY (VALUES((POWER(12,GS.value))))V(I)
         JOIN (VALUES(0,'0'),
                     (1,'1'),
                     (2,'2'),
                     (3,'3'),
                     (4,'4'),
                     (5,'5'),
                     (6,'6'),
                     (7,'7'),
                     (8,'8'),
                     (9,'9'),
                     (10,'X'),
                     (11,'E'))DD(Dec,Duo) ON (ABS(@I) / V.I) % 12 = DD.Dec
    WHERE (ABS(@I) / V.I) > 0
       OR GS.value = 0;
GO

SELECT *
FROM (VALUES(10),(19),(131),(1654329))V(I)
     CROSS APPLY dbo.DecimalToDuodecimal (V.I);

This assumes you are using SQL Server 2022+; if not you'll need to replace GENERATE_SERIES with a different solution, such as a tally.

There were multiple notations described for 10 and 11, however, I use X (10), and E (11) here as they are ASCII characters. You could, however, also store the values in a table, and JOIN to that, which (if you wanted) would allow you store different characters and return different notation methods. I use a UTF-8 collation for DSGB as well, to keep the bytes storage small (otherwise an nvarchar would be needed, meaning 2+ bytes for all characters, rather than 3 bytes for ↊ (10) and ↋ (11) only:

DROP TABLE IF EXISTS dbo.DuoDecimalCharacter;
GO
CREATE TABLE tbl.DuoDecimalCharacter (Dec tinyint NOT NULL,
                                      Duo varchar(3) COLLATE Latin1_General_100_CI_AS_SC_UTF8 NOT NULL,--UTF-8 to avoid 2 bytes storage for 0-9
                                      Notation varchar(10) NOT NULL,
                                      CONSTRAINT PK_DuoDecimalCharacter PRIMARY KEY (Dec,Notation),
                                      CONSTRAINT Chk_DuoDecimalCharacter_Dec CHECK (Dec >= 0 AND Dec <= 12)); 

GO
INSERT INTO dbo.DuoDecimalCharacter (Dec,
                                     Duo,
                                     Notation)
VALUES (0, N'0', 'XE'),
       (0, N'0', 'DSA'),
       (0, N'0', 'Telephone'),
       (0, N'0', 'DSGB'),
       (1, N'1', 'XE'),
       (1, N'1', 'DSA'),
       (1, N'1', 'Telephone'),
       (1, N'1', 'DSGB'),
       (2, N'2', 'XE'),
       (2, N'2', 'DSA'),
       (2, N'2', 'Telephone'),
       (2, N'2', 'DSGB'),
       (3, N'3', 'XE'),
       (3, N'3', 'DSA'),
       (3, N'3', 'Telephone'),
       (3, N'3', 'DSGB'),
       (4, N'4', 'XE'),
       (4, N'4', 'DSA'),
       (4, N'4', 'Telephone'),
       (4, N'4', 'DSGB'),
       (5, N'5', 'XE'),
       (5, N'5', 'DSA'),
       (5, N'5', 'Telephone'),
       (5, N'5', 'DSGB'),
       (6, N'6', 'XE'),
       (6, N'6', 'DSA'),
       (6, N'6', 'Telephone'),
       (6, N'6', 'DSGB'),
       (7, N'7', 'XE'),
       (7, N'7', 'DSA'),
       (7, N'7', 'Telephone'),
       (7, N'7', 'DSGB'),
       (8, N'8', 'XE'),
       (8, N'8', 'DSA'),
       (8, N'8', 'Telephone'),
       (8, N'8', 'DSGB'),
       (9, N'9', 'XE'),
       (9, N'9', 'DSA'),
       (9, N'9', 'Telephone'),
       (9, N'9', 'DSGB'),
       (10, N'X', 'XE'),
       (10, N'X', 'DSA'),
       (10, N'*', 'Telephone'),
       (10, N'↊', 'DSGB'),
       (11, N'E', 'XE'),
       (11, N'Z', 'DSA'),
       (11, N'#', 'Telephone'),
       (11, N'↋', 'DSGB');

GO

CREATE OR ALTER FUNCTION fn.DecimalToDuodecimal (@I int, @Notation varchar(10))
RETURNS table
AS
    RETURN SELECT CASE SIGN(@I) WHEN -1 THEN '-' ELSE '' END +
                  STRING_AGG(DDC.Duo, '') WITHIN GROUP(ORDER BY GS.value DESC) AS Duodecimal,
                  DDC.Notation
           FROM GENERATE_SERIES(0, 8) GS
                CROSS APPLY (VALUES ((POWER(12, GS.value)))) V (I)
                JOIN tbl.DuoDecimalCharacter DDC ON (ABS(@I) / V.I) % 12 = DDC.Dec
                                                AND DDC.Notation = @Notation
           WHERE (ABS(@I) / V.I) > 0
              OR GS.value = 0
           GROUP BY Notation;
GO

SELECT *
FROM (VALUES (-1),
             (0),
             (10),
             (19),
             (131),
             (1654329)) V (I)
     CROSS APPLY dbo.DecimalToDuodecimal(V.I, 'DSGB');