Neoview SQL Reference Manual (R2.4 SP2)

CONVERTTOHEX Function
The CONVERTTOHEX function converts the specified value expression to hexadecimal for
display purposes.
CONVERTTOHEX is a Neoview SQL extension.
CONVERTTOHEX (expression)
expression
is any numeric, character, datetime, or interval expression.
The primary purpose of the CONVERTTOHEX function is to eliminate any doubt as to the exact
value in a column. It is particularly useful for character expressions where some characters may
be from character sets that are not supported by the client terminal’s locale or may be control
codes or other non-displayable characters.
Considerations for CONVERTTOHEX
Although CONVERTTOHEX is usable on datetime and interval expressions, the displayed output
shows the internal value and is, consequently, not particularly meaningful to general users and
is subject to change in future releases.
CONVERTTOHEX returns ASCII characters in ISO8859-1 encoding on a Neoview platform that
has the ISO88591 or SJIS configuration. It returns ASCII characters in UCS2 encoding on a Neoview
platform that has the Unicode configuration. For more information on the Neoview character
set configurations, see the Neoview Character Sets Administrator's Guide.
Examples of CONVERTTOHEX
• Display the contents of a smallint, integer, and largeint in hexadecimal:
CREATE TABLE EG (S1 smallint, I1 int, L1 largeint);
INSERT INTO EG VALUES( 37, 2147483647, 2305843009213693951);
SELECT CONVERTTOHEX(S1), CONVERTTOHEX(I1), CONVERTTOHEX(L1) from EG;
(EXPR) (EXPR) (EXPR)
------ -------- ----------------
0025 7FFFFFFF 1FFFFFFFFFFFFFFF
• Display the contents of a CHAR(4) column, a VARCHAR(4) column and a CHAR(4) column
that uses the UCS2 character set. Note that the varchar column does not have a trailing space
character as the fixed-length columns have:
CREATE TABLE EG_CH (FC4 CHAR(4), VC4 VARCHAR(4), FC4U CHAR(4) CHARACTER SET UCS2);
INSERT INTO EG_CH values('ABC', 'abc', _UCS2'abc');
SELECT CONVERTTOHEX(FC4), CONVERTTOHEX(VC4), CONVERTTOHEX(FC4U) from EG_CH;
(EXPR) (EXPR) (EXPR)
-------- -------- ----------------
41424320 616263 0061006200630020
• Display the internal values for a DATE column, a TIME column, a TIMESTAMP(2) column,
and a TIMESTAMP(6) column:
CREATE TABLE DT (D1 date, T1 time, TS1 timestamp(2), TS2 timestamp(6) )
no partition;
INSERT INTO DT values(current_date, current_time, current_timestamp,
current_timestamp);
SELECT CONVERTTOHEX(D1), CONVERTTOHEX(T1), CONVERTTOHEX(TS1),
CONVERTTOHEX(TS2) from DT;
(EXPR) (EXPR) (EXPR) EXPR)
354 SQL Functions and Expressions