SQL Reference

HEX

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

The schema is SYSIBM.

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

The argument can be an expression that is a value of any built-in data type with a maximum length of 16 336 bytes.

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 code page is the database 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 and length of character string arguments.

• Character string
  • Fixed length not greater than 127
    • Result is a character string of fixed length twice the defined length of the argument.
  • Fixed length greater than 127
    • Result is a character string of varying length twice the defined length of the argument.
  • Varying length
    • Result is a character string of varying length with maximum length twice the defined maximum length of the argument.
• Graphic string
  • Fixed length not greater than 63
    • Result is a character string of fixed length four times the defined length of the argument.
• Fixed length greater than 63
  • Result is a character string of varying length four times the defined length of the argument.
• Varying length
  • Result is a character string of varying length with maximum length four times the defined maximum length of the argument.

Examples:

Assume the use of a DB2 for AIX application server for the following examples.

• 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').

• 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-character string '42'.
• Suppose COL_3 is a column with a data type of decimal(6,2) and a value of 40.1. An eight-character string '0004010C' is the result of applying the HEX function to the internal representation of the decimal value, 40.1.