HEX scalar function

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

Read syntax diagramSkip visual syntax diagramHEX(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 type1 Length attribute2 Result data type
CHAR(A) or BINARY(A) A<128 CHAR(A*2)
CHAR(A) or BINARY(A) A>127 VARCHAR(A*2)
VARCHAR(A), VARBINARY(A), CLOB(A), or BLOB(A) A<16337 VARCHAR(A*2)
GRAPHIC(A) A<64 CHAR(A*2*2)
GRAPHIC(A) A>63 VARCHAR(A*2*2)
VARGRAPHIC(A) or DBCLOB(A) A<8169 VARCHAR(A*2*2)
CHAR(A CODEUNITS32) A<64 VARCHAR(A*4*2)
VARCHAR(A CODEUNITS32) or CLOB(A CODEUNITS32) A<4085 VARCHAR(A*4*2)
GRAPHIC(A CODEUNITS32) A<64 VARCHAR(A*2*2*2)
VARGRAPHIC(A CODEUNITS32) or DBCLOB(A CODEUNITS32) A<4085 VARCHAR(A*2*2*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.