HEX

The HEX function returns a hexadecimal representation of a value.


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

expression: An expression that returns a value of any built-in data type other than a character or binary string with a length attribute greater than 16 336 or a graphic string with a length attribute greater than 8168.

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

The result is a string of hexadecimal digits, the first two digits represent the first byte of the argument, the next two digits represent the second byte of the argument, and so forth. If the argument is a datetime value, the result is the hexadecimal representation of the internal form of the argument.¹

Start of change If the argument is not a graphic string, the actual length of the result is twice the length of the argument. If the argument is a graphic string, the actual length of the result is four times the length of the argument. If the data type of the result is varying length, the length is limited to the maximum length of the data type. The length of the argument is the value that would be returned if the argument were passed to the LENGTH scalar function. For more information, see LENGTH. End of change

The data type and length attribute of the result depends on the attributes of the argument:

If the argument is not a string, the result is CHAR with a length attribute that is twice the length of the argument.
If the argument is a fixed-length character string with a length attribute that is less than one half the maximum length attribute of CHAR, the result is CHAR with a length attribute that is twice the length attribute of the argument. If the argument is a fixed-length graphic string with a length attribute that is less than one fourth the maximum length attribute of CHAR, the result is CHAR with a length attribute that is four times the length attribute of the argument. For more information about the product-specific maximum length, see Table 3.
Otherwise, the result is VARCHAR whose length attribute depends on the following:
- If the argument is a character or binary string, the length attribute of the result is the minimum of twice the length attribute of the argument and the maximum length of the data type.
- If the argument is a graphic string, the length attribute of the result is the minimum of four times the length attribute of the argument and the maximum length of the data type.

The length attribute of the result cannot be greater than the product-specific length attribute of CHAR or VARCHAR. See Table 3 for more information.

The CCSID of the string is the default SBCS CCSID at the current server.

Example

Use the HEX function to return a hexadecimal representation of the education level for each employee.
```
  SELECT FIRSTNME, MIDINIT, LASTNAME, HEX(EDLEVEL)
    FROM EMPLOYEE
```

¹ This hexadecimal representation for DATE, TIMESTAMP, and NUMERIC data types is different from other database products because the internal form for these data types is different.