How to Convert Integers to Hexadecimal, Octal and Binary (and Vice Versa) in SQL Server

Hey guys!
Goodnight.

In this post I will demonstrate how to perform conversions between decimal numbers (base 10) to hexadecimal numbers (base 16) and vice versa using simple SQL Server functions to perform these tasks and at the end, I will provide a very interesting function that allows you to convert a number in base 10 to another number in any base (base 2 to 99)

How to convert integer to hexadecimal

Using the function below, you can easily convert an integer to a hexadecimal digit.

CREATE FUNCTION dbo.fncInteiro_Para_Hexadecimal(
    @Numero INT
)
RETURNS VARCHAR(50)
AS
BEGIN


    DECLARE 
        @Sequencia VARCHAR(16) = '0123456789ABCDEF',
        @Resultado VARCHAR(50),
        @Digito CHAR(1)
    
    
    SET @Resultado = SUBSTRING(@Sequencia, (@Numero % 16) + 1, 1)

    WHILE (@Numero > 0)
    BEGIN
    
        SET @Digito = SUBSTRING(@Sequencia, ((@Numero / 16) % 16) + 1, 1)

        SET @Numero = @Numero / 16
        IF (@Numero != 0 )
            SET @Resultado = @Digito + @Resultado
            
    END 


    RETURN @Resultado

    
END

or we can use a simple CONVERT, but that doesn't bring the result with the formatting I would like:

SELECT CONVERT(VARBINARY(8), 16777215)

or even using the fn_varbintohexstr system function:

SELECT master.dbo.fn_varbintohexstr(CONVERT(VARBINARY,CONVERT(INT, 257)))

Usage examples:

How to convert hexadecimal number to integer

Using the function below, you can quickly convert a hexadecimal digit to an integer.

CREATE FUNCTION [dbo].[fncHexadecimal_Para_Inteiro](@hex VARCHAR(64))
RETURNS varchar(50)
AS
BEGIN

    RETURN CAST(CONVERT(VARBINARY,'0x'+RIGHT('00000000'+REPLACE(@hex,'x',''),8),1) AS INT)
    
END

We can also perform this task with another simple CONVERT:

SELECT CONVERT(INT,CONVERT(VARBINARY(4),'FA',2))

Example of use:

How to convert binary number to decimal

Using the function below, we can convert binary numbers to decimal:

CREATE FUNCTION [dbo].[fncBinario_para_Decimal] (
    @Numero_Binario varchar(255)
)
RETURNS BIGINT
AS
BEGIN

    DECLARE @Contador TINYINT = 1
    DECLARE @Tamanho TINYINT = LEN(@Numero_Binario)
    DECLARE @Resultado BIGINT = CAST(SUBSTRING(@Numero_Binario, @Tamanho, 1) AS BIGINT)

    WHILE(@Contador < @Tamanho) 
    BEGIN
        
        SET @Resultado = @Resultado + POWER(CAST(SUBSTRING(@Numero_Binario, @Tamanho - @Contador, 1) * 2 AS BIGINT), @Contador)
        SET @Contador = @Contador + 1
        
    END

    
    RETURN @Resultado	
    

END

Examples of use:

How to convert a decimal number to a generic base

Finally, this is the final solution for converting decimal numbers to different bases, as the function allows you to convert to binary, hexadecimal, octal, etc.

CREATE FUNCTION dbo.fncConverte_Numero_Base_Generica (
    @Numero AS BIGINT,
    @Base AS INT
)
RETURNS VARCHAR(MAX)
AS
BEGIN

    DECLARE
        @Caracteres VARCHAR(99) = '0123456789abcdefghijklmnopqrstuvwxyz!@#$%&*()_-+={}[]^~:;?/\<>|.,¹²³£¢¬º°¨"áéíóúàèìòùãõâêîôûäëïöüñç',
        @Resultado VARCHAR(MAX) = ''

    
    IF (@Numero < 0 OR @Base < 2 OR @Base > 99)
       RETURN NULL

    
    WHILE (@Numero > 0)
    BEGIN
    
      SELECT
            @Resultado = SUBSTRING(@Caracteres, @Numero % @Base + 1, 1) + @Resultado,
            @Numero = @Numero / @Base;
            
    END

    
    RETURN UPPER(@Resultado)
    

END

Examples of use:

Thanks for visiting and see you next time!

How to convert integer to Hexadecimal in SQL Server how to convert number hex octal binary binary

How to convert integer to Hexadecimal in SQL Server how to convert number hex octal binary binary