Hexadecimal notation for numerals

Tip

Find a more recent version at Firebird 5.0 Language Reference: Hexadecimal Notation for Numbers

Available in: DSQL, PSQL

Added in: 2.5

Description: In Firebird 2.5 and up, integer values can be entered in hexadecimal notation. Numbers with 1–8 hex digits will be interpreted as INTEGERs; numbers with 9–16 hex digits as BIGINTs.

Syntax:

0{x|X}<hexdigits>

<hexdigits>  ::=  1–16 of <hexdigit>
<hexdigit>   ::=  one of 0..9, A..F, a..f

Examples:

select 0x6FAA0D3 from rdb$database           -- returns 117088467
select 0x4F9 from rdb$database               -- returns 1273
select 0x6E44F9A8 from rdb$database          -- returns 1850014120
select 0x9E44F9A8 from rdb$database          -- returns -1639646808 (an INTEGER)
select 0x09E44F9A8 from rdb$database         -- returns 2655320488 (a BIGINT)
select 0x28ED678A4C987 from rdb$database     -- returns 720001751632263
select 0xFFFFFFFFFFFFFFFF from rdb$database  -- returns -1

Value ranges:

Hex numbers in the range 0 .. 7FFF FFFF are positive INTEGERs with values between 0 .. 2147483647 decimal. You can force them to BIGINT by prepending enough zeroes to bring the total number of hex digits to nine or above, but that only changes their type, not their value.
Hex numbers between 8000 0000 .. FFFF FFFF require some attention:
- When written with eight hex digits, as in 0x9E44F9A8, they are interpreted as 32-bit INTEGER values. Since their leftmost bit (sign bit) is set, they map to the negative range -2147483648 .. -1 decimal.
- With one or more zeroes prepended, as in 0x09E44F9A8, they are interpreted as 64-bit BIGINTs in the range 0000 0000 8000 0000 .. 0000 0000 FFFF FFFF. The sign bit isn't set now, so they map to the positive range 2147483648 .. 4294967295 decimal.
Thus, in this range – and in this range only – prepending a mathematically insignificant 0 results in a totally different value. This is something to be aware of.
Hex numbers between 1 0000 0000 .. 7FFF FFFF FFFF FFFF are all positive BIGINTs.
Hex numbers between 8000 0000 0000 0000 .. FFFF FFFF FFFF FFFF are all negative BIGINTs.