4.1.1. Constants
A constant is a value that is supplied directly in an SQL statement, not derived from an expression, a parameter, a column reference nor a variable. It can be a string or a number.
String Constants (Literals)
A string constant — or string literal — is a series of characters enclosed between a pair of apostrophes (“single quotes”). The maximum length of a string is 32,767 bytes; the maximum character count will be determined by the number of bytes used to encode each character.
|
The character set of a string constant is assumed to be the same as the character set of its destined storage.
String Constants in Hexadecimal Notation
From Firebird 2.5 forward, string literals can be entered in hexadecimal notation, so-called “binary strings”. Each pair of hex digits defines one byte in the string. Strings entered this way will have character set OCTETS
by default, but the introducer syntax can be used to force a string to be interpreted as another character set.
Syntax
{x|X}'<hexstring>'
<hexstring> ::= an even number of <hexdigit>
<hexdigit> ::= one of 0..9, A..F, a..f
Examples
select x'4E657276656E' from rdb$database
-- returns 4E657276656E, a 6-byte 'binary' string
select _ascii x'4E657276656E' from rdb$database
-- returns 'Nerven' (same string, now interpreted as ASCII text)
select _iso8859_1 x'53E46765' from rdb$database
-- returns 'Säge' (4 chars, 4 bytes)
select _utf8 x'53C3A46765' from rdb$database
-- returns 'Säge' (4 chars, 5 bytes)
Notes The client interface determines how binary strings are displayed to the user. The isql utility, for example, uses upper case letters A-F, while FlameRobin uses lower case letters. Other client programs may use other conventions, such as displaying spaces between the byte pairs: The hexadecimal notation allows any byte value (including 00) to be inserted at any position in the string. However, if you want to coerce it to anything other than OCTETS, it is your responsibility to supply the bytes in a sequence that is valid for the target character set. |
Introducer Syntax for String Literals
If necessary, a string literal may be preceded by a character set name, itself prefixed with an underscore “_”. This is known as introducer syntax. Its purpose is to inform the engine about how to interpret and store the incoming string.
Example
INSERT INTO People
VALUES (_ISO8859_1 'Hans-Jörg Schäfer')
Number Constants (Literals)
A number constant — or number literal — is any valid number in a supported notation:
In SQL, for numbers in the standard decimal notation, the decimal point is always represented by period (
.
, full-stop, dot) character and thousands are not separated. Inclusion of commas, blanks, etc. will cause errors.Exponential notation is supported. For example, 0.0000234 can be expressed as
2.34e-5
.Hexadecimal notation is supported by Firebird 2.5 and higher versions — see below.
Hexadecimal Notation for Numbers
From Firebird 2.5 forward, integer values can be entered in hexadecimal notation. Numbers with 1-8 hex digits will be interpreted as type INTEGER
; numbers with 9-16 hex digits as type BIGINT
.
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
Hexadecimal Value Ranges
Hex numbers in the range 0 .. 7FFF FFFF are positive
INTEGER
s with values between 0 .. 2147483647 decimal. To coerce a number toBIGINT
, prepend enough zeroes to bring the total number of hex digits to nine or above. That changes the type but not the value.Hex numbers between 8000 0000 .. FFFF FFFF require some attention:
When written with eight hex digits, as in
0x9E44F9A8
, a value is interpreted as 32-bitINTEGER
. Since the leftmost bit (sign bit) is set, it maps to the negative range -2147483648 .. -1 decimal.With one or more zeroes prepended, as in
0x09E44F9A8
, a value is interpreted as 64-bitBIGINT
in the range 0000 0000 8000 0000 .. 0000 0000 FFFF FFFF. The sign bit is not set now, so they map to the positive range 2147483648 .. 4294967295 decimal.
Thus, in this range — and only in this range — 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
BIGINT
.Hex numbers between 8000 0000 0000 0000 .. FFFF FFFF FFFF FFFF are all negative
BIGINT
.A SMALLINT cannot be written in hex, strictly speaking, since even
0x1
is evaluated asINTEGER
. However, if you write a positive integer within the 16-bit range0x0000
(decimal zero) to0x7FFF
(decimal 32767) it will be converted toSMALLINT
transparently.It is possible to write to a negative
SMALLINT
in hex, using a 4-byte hex number within the range0xFFFF8000
(decimal -32768) to0xFFFFFFFF
(decimal -1).