A string literal is a series of characters enclosed between a pair of apostrophes (single quotes). The maximum length of a string literal is 32,765 for CHAR/VARCHAR, or 65,533 bytes for BLOB; 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.

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

   |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)

  |<alternative string literal> ::=
  |    { q | Q } <quote> <start char> [<char> ...] <end char> <quote>

  |select q'{abc{def}ghi}' from rdb$database;        -- result: abc{def}ghi
  |select q'!That's a string!' from rdb$database;    -- result: That's a string

  |INSERT INTO People
  |VALUES (_ISO8859_1 'Hans-Jörg Schäfer')

A 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 character (., full-stop, dot); 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.

The format of the literal decides the type (<d> for a decimal digit, <h> for a hexadecimal digit):

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

  |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

A Boolean literal is one of TRUE, FALSE or UNKNOWN.

Formally, the SQL standard defines datetime literals as a prefix DATE, TIME and TIMESTAMP followed by a string literal with a datetime format. Historically, Firebird documentation has referred to these datetime literals as "shorthand casts".

In Firebird 4.0, the use of datetime mnemonics in datetime literals (e.g. DATE 'TODAY') is no longer allowed.

Datetime Literal Syntax

  |<datetime_literal> ::=
  |    DATE '<date_format>'
  |  | TIME { '<time_format>' | '<time_tz_format>' }
  |  | TIMESTAMP { '<timestamp_format>' | '<timestamp_tz_format>' }

Datetime Format Syntax

   |<date_format> ::=
   |      [YYYY<p>]MM<p>DD
   |    | MM<p>DD[<p>{ YYYY | YY }]
   |    | DD<p>MM[<p>{ YYYY | YY }]
   | 
   |<time_format> ::= HH[:mm[:SS[<f>NNNN]]]
   | 
   |<timestamp_format> ::= <date_format> [<space> <time_format>]
   | 
   |<time_zone> ::=
   |    { + | - }HH:MM
   |  | time zone name (e.g. Europe/Berlin)
   | 
   |<time_tz_format> ::= <time_format> [<space>] <time_zone>
   | 
   |<timestamp_tz_format> ::= <timestamp_format> [<space>] <time_zone>
   | 
   |<p> ::= whitespace | . | - | /
   |<f> ::= : | .

Example

   |-- 1
   |  UPDATE PEOPLE
   |  SET AGECAT = 'SENIOR'
   |  WHERE BIRTHDATE < DATE '1-Jan-1943';
   |-- 2
   |  INSERT INTO APPOINTMENTS
   |  (EMPLOYEE_ID, CLIENT_ID, APP_DATE, APP_TIME)
   |  VALUES (973, 8804, DATE '1-Jan-2021' + 2, TIME '16:00');
   |-- 3
   |  NEW.LASTMOD = TIMESTAMP '1-Jan-2021 16:00';

SQL Operators are divided into four types. Each operator type has a precedence, a ranking that determines the order in which operators and the values obtained with their help are evaluated in an expression. The higher the precedence of the operator type is, the earlier it will be evaluated. Each operator has its own precedence within its type, that determines the order in which they are evaluated in an expression.

Operators with the same precedence are evaluated from left to right. To force a different evaluation order, operations can be grouped by means of parentheses.

  |SELECT LAST_NAME || ', ' || FIRST_NAME AS FULL_NAME
  |FROM EMPLOYEE

  |UPDATE T
  |    SET A = 4 + 1/(B-C)*D

  |IF (SALARY > 1400) THEN
  |…

  |IF (A < B OR (A > C AND A > D) AND NOT (C = D)) THEN …

Available inDSQL, PSQL

Result typeBIGINT — dialect 2 and 3 INTEGER — dialect 1

Syntax

  |NEXT VALUE FOR sequence-name

NEXT VALUE FOR returns the next value of a sequence. SEQUENCE is the SQL-standard term for what is historically called a generator in Firebird and its ancestor, InterBase. The NEXT VALUE FOR operator is equivalent to the legacy GEN_ID (…​, increment) function with increment the increment stored in the metadata of the sequence. It is the recommended syntax for retrieving the next sequence value.

Example

  |NEW.CUST_ID = NEXT VALUE FOR CUSTSEQ;

See alsoSEQUENCE (GENERATOR), GEN_ID()

Available inDSQL, PSQL

Syntax

  |<at expr> ::= <expr> AT { TIME ZONE <time zone string> | LOCAL }

The AT expression expresses a datetime value in a different time zone, while keeping the same UTC instant.

AT translates a time/timestamp value to its corresponding value in another time zone. If LOCAL is used, the value is converted to the session time zone.

When expr is a WITHOUT TIME ZONE type, expr is first converted to a WITH TIME ZONE in the session time zone and then transformed to the specified time zone.

Examples

  |select time '12:00 GMT' at time zone '-03:00' from rdb$database;
  |select current_timestamp at time zone 'America/Sao_Paulo' from rdb$database;
  |select timestamp '2018-01-01 12:00 GMT' at local from rdb$database;

Available inDSQL, PSQL

The CASE construct returns a single value from a number of possible values. Two syntactic variants are supported:

• The simple CASE, comparable to a case construct in Pascal or a switch in C

• The searched CASE, which works like a series of if …​ else if …​ else if clauses.

  |…
  |CASE <test-expr>
  |  WHEN <expr> THEN <result>
  |  [WHEN <expr> THEN <result> ...]
  |  [ELSE <defaultresult>]
  |END
  |…

   |SELECT
   |  NAME,
   |  AGE,
   |  CASE UPPER(SEX)
   |    WHEN 'M' THEN 'Male'
   |    WHEN 'F' THEN 'Female'
   |    ELSE 'Unknown'
   |  END GENDER,
   |RELIGION
   |    FROM PEOPLE

  |CASE
  |  WHEN <bool_expr> THEN <result>
  |  [WHEN <bool_expr> THEN <result> …]
  |  [ELSE <defaultresult>]
  |END

  |CANVOTE = CASE
  |  WHEN AGE >= 18 THEN 'Yes'
  |  WHEN AGE < 18 THEN 'No'
  |  ELSE 'Unsure'
  |END

Expressions in this list will always return NULL:

  |1 + 2 + 3 + NULL
  |'Home ' || 'sweet ' || NULL
  |MyField = NULL
  |MyField <> NULL
  |NULL = NULL
  |not (NULL)

If it seems difficult to understand why, remember that NULL is a state that stands for unknown.

It has already been shown that NOT (NULL) results in NULL. The interaction is a bit more complicated for the logical AND and logical OR operators:

  |NULL or false  → NULL
  |NULL or true   → true
  |NULL or NULL   → NULL
  |NULL and false → false
  |NULL and true  → NULL
  |NULL and NULL  → NULL

Examples

   |(1 = NULL) or (1 <> 1)    -- returns NULL
   |(1 = NULL) or FALSE       -- returns NULL
   |(1 = NULL) or (1 = 1)     -- returns TRUE
   |(1 = NULL) or TRUE        -- returns TRUE
   |(1 = NULL) or (1 = NULL)  -- returns NULL
   |(1 = NULL) or UNKNOWN     -- returns NULL
   |(1 = NULL) and (1 <> 1)   -- returns FALSE
   |(1 = NULL) and FALSE      -- returns FALSE
   |(1 = NULL) and (1 = 1)    -- returns NULL
   |(1 = NULL) and TRUE       -- returns NULL
   |(1 = NULL) and (1 = NULL) -- returns NULL
   |(1 = NULL) and UNKNOWN    -- returns NULL

A subquery can be correlated. A query is correlated when the subquery and the main query are interdependent. To process each record in the subquery, it is necessary to fetch a record in the main query; i.e. the subquery fully depends on the main query.

Sample Correlated Subquery

  |SELECT *
  |FROM Customers C
  |WHERE EXISTS
  |  (SELECT *
  |   FROM Orders O
  |   WHERE C.cnum = O.cnum
  |     AND O.adate = DATE '10.03.1990');

When subqueries are used to get the values of the output column in the SELECT list, a subquery must return a scalar result (see below).

Subqueries used in search predicates, other than existential and quantified predicates, must return a scalar result; that is, not more than one column from not more than one matching row or aggregation. If the result would return more, a run-time error will occur (Multiple rows in a singleton select…​).