Introduction

Are you struggling to convert decimal numbers to hexadecimal in Oracle SQL? Don’t worry; you are not alone. Convert decimal to hexadecimal in Oracle SQL is a common requirement in database programming. In this article, we will guide you on how to convert decimal numbers to hexadecimal in Oracle SQL.

What are Decimal and Hexadecimal Number Systems?

Before we delve into the conversion process, let’s briefly discuss the decimal and hexadecimal number systems. The decimal number system is the most commonly used number system, consisting of ten digits (0 to 9). The place value of each digit is ten times greater than the digit to its right.

On the other hand, the hexadecimal number system is a base-16 number system consisting of 16 digits (0 to 9 and A to F). The place value of each digit is 16 times greater than the digit to its right.

Conversion of Decimal to Hexadecimal in Oracle SQL

To convert decimal to hexadecimal in Oracle SQL, we can use the built-in function TO_CHAR(). The TO_CHAR() function converts a number or date to a string.

Syntax

The syntax of the TO_CHAR() function for converting decimal to hexadecimal is as follows:

TO_CHAR(decimal_number, 'X') 

Here, decimal_number is the decimal number you want to convert to hexadecimal.

Example (1)

SELECT TO_CHAR(10, 'X') AS HEX_VALUE
FROM DUAL;

Output:

In the above example, we have converted the decimal number 10 to its equivalent hexadecimal value, ‘A’, but notice that oracle is added a leading white space to the result due to the space for the leading minus sign.

Conversion of Large Decimal Numbers to Hexadecimal

What if the decimal number you want to convert to hexadecimal is too large? The TO_CHAR() function may not give the correct result in such cases.

We must use the HEXTORAW() and TO_CHAR() functions to handle large decimal numbers. The HEXTORAW() function converts a hexadecimal string to its equivalent raw data.

Syntax

The syntax of the HEXTORAW() function is as follows:

HEXTORAW(hexadecimal_string)

Example (2)

SELECT RAWTOHEX (HEXTORAW (TO_CHAR (456789, 'fmXXXXXXXX'))) AS HEX_VALUE
FROM DUAL;

Output:

In the above example, we have converted the decimal number 456789 to its equivalent hexadecimal value, ‘06F855‘.

Conversion of Negative Decimal Numbers to Hexadecimal

What if the decimal number you want to convert to hexadecimal is negative? In such cases, we need to add 4294967296 (2\^32) to the decimal number and then convert it to hexadecimal using the TO_CHAR() function.

Example (3)

SELECT TO_CHAR(-10 + 4294967296, 'XXXXXXXX') AS HEX_VALUE 
FROM DUAL;

Output:

In the above example, we have converted the negative decimal number -10 to its equivalent hexadecimal value ‘FFFFFFF6’.

Eliminate Leading Whitespaces

By default, Oracle adds white space to the result of the decimal number conversion to Hex, and this is due to the space for the leading minus sign, and to remove this whitespace, we can use fm when converting the number to Hex.

SELECT TO_CHAR(155, 'fmXX') HEX_WITHOUT_SPACE
FROM DUAL;

Output:

In the above example, we converted the number 155 to Hex and added the “fm” prefix to eliminate whitespaces in the output.

Convert the decimal to hexadecimal with Uppercase and Lowercase

If we need to print the result in uppercase and lowercase, and in this case, we need to pass the argument XX as upper to print the hexadecimal number in uppercase or “xx” to print it in lowercase as in the example below.

Example (6)

SELECT TO_CHAR (219, 'fmXX') HEX_UPPERCASE, TO_CHAR (219, 'fmxx') HEX_LOWERCASE
FROM DUAL;

Output:

In the above example, we converted the number219 to Hex one time in uppercase and another in lowercase.

Conclusion

In this article, we have learned how to convert decimal numbers to hexadecimal in Oracle SQL. We have discussed the TO_CHAR() function and how to handle large and negative decimal numbers. With this knowledge, you can easily convert decimal numbers to hexadecimal in Oracle SQL.

FAQs

1. What is a hexadecimal number system? A hexadecimal number system is a base-16 number system consisting of 16 digits (0 to 9 and A to F).
2. What is the decimal number system? The decimal number system is a base-10 number system consisting of 10 digits (0 to 9).
3. What is the purpose of converting decimal to hexadecimal in Oracle SQL? Converting decimal to hexadecimal is often required in database programming for various applications, such as cryptography and networking.