Function to Convert Decimal Number into Binary, Ternary, and Octal

In this article I am sharing user defined function to convert a decimal number into Binary, Ternary, and Octalequivalent.

IF OBJECT_ID(N'dbo.udfGetNumbers', N'TF') IS NOT NULL
DROP FUNCTION dbo.udfGetNumbers
GO


CREATE FUNCTION dbo.udfGetNumbers
(@base [int], @lenght [int])
RETURNS @NumbersBaseN TABLE
(
  decNum [int] PRIMARY KEY NOT NULL,
  NumBaseN [varchar](50) NOT NULL
)
AS
BEGIN
  WITH tblBase AS
  (
    SELECT CAST(0 AS VARCHAR(50)) AS baseNum
    UNION ALL
    SELECT CAST((baseNum + 1) AS 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 + num) AS VARCHAR(50))
    FROM numbers CROSS JOIN tblBase t2
    WHERE LEN(NUM) < @lenght
  )

  INSERT INTO @NumbersBaseN
  SELECT ROW_NUMBER() OVER (ORDER BY NUM) -1 AS rowID, NUM
  FROM numbers WHERE LEN(NUM) > @lenght - 1

  OPTION (MAXRECURSION 0);
  RETURN
END
GO


-- Unit Test --
-- Example with decimal, binary, ternary and octal


SELECT
   U1.decNum   AS Base10,
   U1.NumBaseN AS Base2,
   U2.NumBaseN AS Base3,
   U3.NumBaseN AS Base8
FROM dbo.udfGetNumbers(2, 10) U1
JOIN dbo.udfGetNumbers(3, 7) U2
  ON u1.decNum = u2.decNum
JOIN dbo.udfGetNumbers(8, 4) U3
  ON u2.decNum = u3.decNum


Here is the output: