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.

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

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

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.

  |0{x|X}<hexdigits>
  | 
  |<hexdigits>  ::=  1-16 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

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 …

AvailableDSQL, PSQL

NEXT VALUE FOR returns the next value of a sequence. SEQUENCE is an SQL-compliant term for a generator in Firebird and its ancestor, InterBase. The NEXT VALUE FOR operator is equivalent to the legacy GEN_ID (…​, 1) function and is the recommended syntax for retrieving the next sequence value.

Syntax for NEXT VALUE FOR

  |NEXT VALUE FOR sequence-name

Example

  |NEW.CUST_ID = NEXT VALUE FOR CUSTSEQ;

See alsoSEQUENCE (GENERATOR), GEN_ID()

AvailableDSQL, PSQL

The CASE construct returns a single value from a number of possible ones. 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

Up to and including Firebird 2.5.x, there is no implementation for a logical (Boolean) data type — that is coming in Firebird 3. However, there are logical expressions (predicates) that can return true, false or unknown.

Examples

  |(1 = NULL) or (1 <> 1) -- returns NULL
  |(1 = NULL) or (1 = 1) -- returns TRUE
  |(1 = NULL) or (1 = NULL) -- returns NULL
  |(1 = NULL) and (1 <> 1) -- returns FALSE
  |(1 = NULL) and (1 = 1) -- returns NULL
  |(1 = NULL) and (1 = NULL) -- 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.

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…​).