Convert An Integer From Base 10 To Another Base (e.g. Hex Or Binary)

• Tweet
• 

        @base can be anything from 2 to 36 (because then we can stick to the alphanumerics 0 to 9 and A to Z).
This work is licensed under a Creative Commons Attribution 2.5 License.

CREATE FUNCTION dbo.f_convert_from_base10
  (@num INT, @base TINYINT)
RETURNS VARCHAR(255) AS 
BEGIN 

  -- Declarations
  DECLARE @string VARCHAR(255)
  DECLARE @return VARCHAR(255)
  DECLARE @finished BIT
  DECLARE @div INT
  DECLARE @rem INT
  DECLARE @char CHAR(1)

  -- Initialise
  SELECT @string   = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'
  SELECT @return   = CASE WHEN @num <= 0 THEN '0' ELSE '' END
  SELECT @finished = CASE WHEN @num <= 0 THEN 1 ELSE 0 END
  SELECT @base     = CASE WHEN @base < 2 OR @base IS NULL THEN 2 WHEN @base > 36 THEN 36 ELSE @base END

  -- Loop
  WHILE @finished = 0
  BEGIN

    -- Do the maths
    SELECT @div = @num / @base
    SELECT @rem = @num - (@div * @base)
    SELECT @char = SUBSTRING(@string, @rem + 1, 1)
    SELECT @return = @char + @return
    SELECT @num = @div

    -- Nothing left?
    IF @num = 0 SELECT @finished = 1

  END

  -- Done
  RETURN @return

END
GO

Example of usage:

SELECT 255 AS [decimal], dbo.f_convert_from_base10(255, 16) AS [hex], dbo.f_convert_from_base10(255, 2) AS [binary], dbo.f_convert_from_base10(255, 36) AS [base 36]

Tags:
• SQL
• sql-server
• sqlserver
• sql_server