17.10 - Hexadecimal Integer Literals - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Data Types and Literals

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1143-171K
Language
English (United States)

Declares a hexadecimal integer literal value.

Hexadecimal literals consist of 0 to 16 hexadecimal digits delimited by a matching pair of apostrophes. Spaces and new line characters are not allowed in a literal.

The modifiers following the X determine the hexadecimal literal data type.

IF a hexadecimal literal uses this form … THEN the data type is … AND the maximum hexadecimal digits is...
'hexadecimal digits'X

'hexadecimal digits'XI

'hexadecimal digits'XI4

INTEGER 8
'hexadecimal digits'XI2 SMALLINT 4
'hexadecimal digits'XI1 BYTEINT 2
'hexadecimal digits'XI8 BIGINT 16

ANSI Compliance

Hexadecimal literals are Teradata extensions to the ANSI SQL:2011 standard.

Syntax

'hexadecimal digits' X [ | [ 1 | 2 | 4 | 8 ] ]
You must type the bold or colored vertical bar.

Syntax Elements

hexadecimal digits
A string of hexadecimal digits, where a hexadecimal digit is a character from 0 to 9, a to f, or A to F.
1
Integers with a BYTEINT data type.
2
Integers with a SMALLINT data type.
4
Integers with an INTEGER data type. This is the default if 1, 2, 4, or 8 is not specified.
8
Integers with a BIGINT data type.

Usage Notes

Hexadecimal integer literals are represented by an odd or even number of hexadecimal digits. The hexadecimal literal is right-justified. For example, the value 1000 can be expressed as any of the following:

'3e8'X
'0003e8'X
'000003e8'X

1000 hex would be '1000'x, which is 16*16*16=4096.

'3e8'x = 3*16*16 + e*16 + 8 and e is 14 so we get 1000.

Note that the literal 1000 would be a SMALLINT whereas all the hex constants shown here are INTEGERs.

Example: Hexadecimal Integer Literal

Consider the following table:

CREATE TABLE id_pairs (region_id INTEGER, region CHAR(20));

Submit the following statement to find the value of the region column where the region_id is a hexadecimal value of 3e8:

SELECT region FROM id_pairs WHERE region_id = '3e8'X;