Hello everybody!
Good evening.
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 in the end I will provide a very interesting function that allows you to convert a number in base 10 to another number from 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 number.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
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'd like:
|
1 |
SELECT CONVERT(VARBINARY(8), 16777215) |
or even using the fn_varbintohexstr system function:
|
1 |
SELECT master.dbo.fn_varbintohexstr(CONVERT(VARBINARY,CONVERT(INT, 257))) |
Examples of use:
How to convert hexadecimal number to integer
Using the function below, you can quickly convert a hexadecimal digit to an integer.
|
1 2 3 4 5 6 7 8 |
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 accomplish this task with another simple CONVERT:
|
1 |
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 decimals:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
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 |
Usage examples:
How to convert a decimal number to a generic base
Finally, this is the final solution for converting decimal numbers to different bases, because the function allows to perform the conversion to binary, hexadecimal, octal, etc.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
CREATE FUNCTION dbo.fncConverte_Numero_Base_Generica ( @Numero AS BIGINT, @Base AS INT ) RETURNS VARCHAR(MAX) AS BEGIN DECLARE @Caracteres VARCHAR(99) = '[email protected]#$%&*()_-+={}[]^~:;?/\<>|.,¹²³£¢¬º°¨"áéíóúàèìòùãõâêîôûäëïöüñç', @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 |
Usage examples:
Thanks for stopping by and see you next time!
How to convert integer to Hexadecimal in SQL Server how to convert binary octal binary number binary
How to convert integer to Hexadecimal in SQL Server how to convert binary octal binary number binary
top!