DB2 10.5 for Linux, UNIX, and Windows

HEX scalar function

The HEX function returns a hexadecimal representation of a value as a character string.


>>-HEX--(--expression--)---------------------------------------><

The schema is SYSIBM.

expression: An expression that returns a value of any built-in data type that is not XML, with a maximum length of 16 336 bytes.

The result of the function is a character string with string units of OCTETS. If the argument can be null, the result can be null; if the argument is null, the result is the null value.

The code page is the section code page.

The result is a string of hexadecimal digits. The first two represent the first byte of the argument, the next two represent the second byte of the argument, and so forth. If the argument is a datetime value or a numeric value the result is the hexadecimal representation of the internal form of the argument. The hexadecimal representation that is returned may be different depending on the application server where the function is executed. Cases where differences would be evident include:

Character string arguments when the HEX function is performed on an ASCII client with an EBCDIC server or on an EBCDIC client with an ASCII server.
Numeric arguments (in some cases) when the HEX function is performed where client and server systems have different byte orderings for numeric values.

The type and length of the result vary based on the type, length, and string units of the character and graphic string arguments.

Table 1. Data type of the result as a function of the data types of the argument data type and the length attribute
Argument data type¹	Length attribute²	Result data type
CHAR(A)	A<128	CHAR(A*2)
CHAR(A)	A>127	VARCHAR(A*2)
VARCHAR(A) or CLOB(A)	A<16337	VARCHAR(A*2)
GRAPHIC(A)	A<64	CHAR(A22)
GRAPHIC(A)	A>63	VARCHAR(A22)
VARGRAPHIC(A) or DBCLOB(A)	A<8169	VARCHAR(A22)
CHAR(A CODEUNITS32)	A<64	VARCHAR(A42)
VARCHAR(A CODEUNITS32) or CLOB(A CODEUNITS32)	A<4085	VARCHAR(A42)
GRAPHIC(A CODEUNITS32)	A<64	VARCHAR(A22*2)
VARGRAPHIC(A CODEUNITS32) or DBCLOB(A CODEUNITS32)	A<4085	VARCHAR(A22*2)
1. If string units are not specified, then the string units for the data type are not CODEUNITS32. 2. The maximum length attributes reflect a data type limit or the limit of 16336 bytes for the input argument.

Examples

Assume the use of a database application server on AIX® or Linux for the following examples.

Example 1: Using the DEPARTMENT table set the host variable HEX_MGRNO (char(12)) to the hexadecimal representation of the manager number (MGRNO) for the 'PLANNING' department (DEPTNAME).
```
   SELECT HEX(MGRNO)
     INTO :HEX_MGRNO
     FROM DEPARTMENT
     WHERE DEPTNAME = 'PLANNING'
```
HEX_MGRNO will be set to '303030303230' when using the sample table (character value is '000020').
Example 2: Suppose COL_1 is a column with a data type of char(1) and a value of 'B'. The hexadecimal representation of the letter 'B' is X'42'. HEX(COL_1) returns a two byte long string '42'.
Example 3: Suppose COL_3 is a column with a data type of decimal(6,2) and a value of 40.1. An eight byte long string '0004010C' is the result of applying the HEX function to the internal representation of the decimal value, 40.1.