15.00 - CHAR2HEXINT - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)
Last Update
2018-09-24

CHAR2HEXINT

Purpose  

Returns the hexadecimal representation for a character string.

Syntax  

where:

 

Syntax element …

Specifies …

character_string_expression

a character string or character string expression for which the hexadecimal representation is to be returned.

Argument Types

Use CHAR2HEXINT on character strings or character string expressions.

By default, Teradata Database performs implicit type conversion on a UDT argument that has an implicit cast that casts between the UDT and a predefined character type.

To define an implicit cast for a UDT, use the CREATE CAST statement and specify the AS ASSIGNMENT clause. For more information on CREATE CAST, see SQL Data Definition Language.

Implicit type conversion of UDTs for system operators and functions, including CHAR2HEXINT, is a Teradata extension to the ANSI SQL standard. To disable this extension, set the DisableUDTImplCastForSysFuncOp field of the DBS Control Record to TRUE. For details, see Utilities: Volume 1 (A-K).

For more information on implicit type conversion of UDTs, see Chapter 13: “Data Type Conversions.”

CHAR2HEXINT is not supported for CLOBs.

Result Type and Attributes

Here are the default attributes for CHAR2HEXINT(character_string_expression):

 

Data Type

Heading

CHARACTER

Char2HexInt(character_string_expression)

The length of the result is twice the length of character_string_expression.

The server character set of the result is LATIN.

CHAR2HEXINT and Literal Strings

You can apply CHAR2HEXINT to a string literal to determine its hexadecimal equivalent.

Character literals are treated as VARCHAR(n) CHARACTER SET UNICODE, where n is the length of the literal.

The following statement and results illustrate how CHAR2HEXINT operates on literal strings:

   SELECT CHAR2HEXINT('123');
   
   Char2HexInt('123')
   -----------------------
   003100320033

Example  

Assume that the system was enabled with Japanese language support during sysinit.

   CREATE TABLE tab1 
               (clatin   CHAR(3)  CHARACTER SET LATIN
               ,cunicode CHAR(3)  CHARACTER SET UNICODE
               ,csjis    CHAR(3)  CHARACTER SET KANJISJIS
               ,cgraphic CHAR(3)  CHARACTER SET GRAPHIC
               ,ckanji1  CHAR(3)  CHARACTER SET KANJI1);
   
   INSERT INTO tab1('abc','abc','abc',_GRAPHIC 'ABC','abc');
   

The bold uppercase LATIN characters in the example represent full width LATIN characters.

CHAR2HEXINT returns the following results for the character strings inserted into tab1.

 

This function …

Returns this result …

CHAR2HEXINT(clatin)

616263

CHAR2HEXINT(cunicode)

006100620063'

CHAR2HEXINT(csjis)

616263

CHAR2HEXINT(cgraphic)

FF41FF42FF43

CHAR2HEXINT(ckanji1)

616263

Example  

To find the internal hexadecimal representation of all table names, submit the following SELECT statement using CHAR2HEXINT.

   SELECT CHAR2HEXINT(TRIM(t.tablename))(FORMAT 'X(30)')
   (TITLE 'Internal Hex Representation of TableName')
   ,t.tablename (TITLE 'TableName')
   FROM dbc.tables T
   WHERE t.tablekind = 'T'
   ORDER BY t.tablename;

Partial output from this SELECT statement is similar to the following report:

   Internal Hex Representation of TableName   TableName
   ----------------------------------------   ----------------
   416363657373526967687473                    AccessRights
   4163634C6F6752756C6554626C                  AccLogRuleTbl
   4163634C6F6754626C                          AccLogTbl
   4163636F756E7473                            Accounts
   4163637467                                  Acctg 
   416C6C                                      All
   436F70496E666F54626C                        CopInfoTbl