HEX scalar function
The HEX function returns a hexadecimal representation of a value as a character string.
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.
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).
HEX_MGRNO will be set toSELECT HEX(MGRNO) INTO :HEX_MGRNO FROM DEPARTMENT WHERE DEPTNAME = 'PLANNING'
303030303230
when using the sample table (character value is000020
). - 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 letterB
is X'42'. HEX(COL_1) returns a two byte long string42
. - 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.