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.

  • Double quotes are NOT VALID for quoting strings. SQL reserves a different purpose for them.

  • If a literal apostrophe is required within a string constant, it is “escaped” by prefixing it with another apostrophe. For example, ‘Mother O’’Reilly’s home-made hooch’.

  • Care should be taken with the string length if the value is to be written to a VARCHAR column. The maximum length for a VARCHAR is 32,765 bytes.

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

  1. {x|X}'<hexstring>'
  3. <hexstring>  ::=  an even number of <hexdigit>
  4. <hexdigit>   ::=  one of 0..9, A..F, a..f

Examples

  1. select x'4E657276656E' from rdb$database
  2. -- returns 4E657276656E, a 6-byte 'binary' string
  4. select _ascii x'4E657276656E' from rdb$database
  5. -- returns 'Nerven' (same string, now interpreted as ASCII text)
  7. select _iso8859_1 x'53E46765' from rdb$database
  8. -- returns 'Säge' (4 chars, 4 bytes)
  10. select _utf8 x'53C3A46765' from rdb$database
  11. -- 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: ‘4E 65 72 76 65 6E’.

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

  1. INSERT INTO People
  2. 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

  1. 0{x|X}<hexdigits>
  3. <hexdigits>  ::=  1-16 of <hexdigit>
  4. <hexdigit>   ::=  one of 0..9, A..F, a..f

Examples

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

Hexadecimal Value Ranges

  • Hex numbers in the range 0 .. 7FFF FFFF are positive INTEGERs with values between 0 .. 2147483647 decimal. To coerce a number to BIGINT, 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-bit INTEGER. 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-bit BIGINT 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 as INTEGER. However, if you write a positive integer within the 16-bit range 0x0000 (decimal zero) to 0x7FFF (decimal 32767) it will be converted to SMALLINT transparently.

    It is possible to write to a negative SMALLINT in hex, using a 4-byte hex number within the range 0xFFFF8000 (decimal -32768) to 0xFFFFFFFF (decimal -1).