Literals (Constants)

A literal — or 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.

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.

Formally, the syntax of a normal string literal is:

The character set of a string constant is assumed to be the same as the character set of its destined storage.

String literals can also 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 be type BINARY (a.k.a. CHAR CHARACTER SET OCTETS) by default, unless the introducer syntax is used to force a string to be interpreted as another character set.

It is possible to use a character, or character pair, other than the doubled (escaped) apostrophe, to embed a quoted string inside another string without the need to escape the quote.The keyword q or Q preceding a quoted string informs the parser that certain left-right pairs or pairs of identical characters within the string are the delimiters of the embedded string literal.

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

A number literal is any valid number in a supported notation:

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

Integer values can also 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;numbers with 17-32 hex digits as type INT128.

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”.

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

SQL operators comprise operators for comparing, calculating, evaluating and concatenating values.

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.

The concatenation operator — two pipe characters known as “double pipe” or ‘||’ — concatenates two character strings to form a single string.Character strings can be literals or values obtained from columns or other expressions.

BLOB_APPEND()

This group also includes comparison predicates BETWEEN, LIKE, CONTAINING, SIMILAR TO and others.

Other Comparison Predicates.

BIGINT — dialect 2 and 3
INTEGER — dialect 1

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.

SEQUENCE (GENERATOR), GEN_ID()

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.

A conditional expression is one that returns different values according to how a certain condition is met.It is composed by applying a conditional function construct, of which Firebird supports several.This section describes only one conditional expression construct: CASE.All other conditional expressions apply internal functions derived from CASE and are described in Conditional Functions.

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

When this variant is used, test-expr is compared to the first expr, second expr and so on, until a match is found, and the corresponding result is returned.If no match is found, defaultresult from the optional ELSE clause is returned.If there are no matches and no ELSE clause, NULL is returned.

The matching works as the “=” operator.That is, if test-expr is NULL, it does not match any expr, not even an expression that resolves to NULL.

The returned result does not have to be a literal value: it might be a field or variable name, compound expression or NULL literal.

A short form of the simple CASE construct is the DECODE function.

The bool_expr expression is one that gives a ternary logical result: TRUE, FALSE or NULL.The first expression to return TRUE determines the result.If no expressions return TRUE, defaultresult from the optional ELSE clause is returned as the result.If no expressions return TRUE and there is no ELSE clause, the result will be NULL.

As with the simple CASE construct, the result need not be a literal value: it might be a field or variable name, a compound expression, or be NULL.

NULL is not a value in SQL, but a state indicating that the value of the element either is unknown or it does not exist.It is not a zero, nor a void, nor an “empty string”, and it does not act like any value.

When you use NULL in numeric, string or date/time expressions, the result will always be NULL.When you use NULL in logical (Boolean) expressions, the result will depend on the type of the operation and on other participating values.When you compare a value to NULL, the result will be unknown.

Consult theFirebird Null Guidefor more in-depth coverage of Firebird’s NULL behaviour.

Expressions in this list will always return 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:

A subquery is a special form of expression that is a query embedded within another query.Subqueries are written in the same way as regular SELECT queries, but they must be enclosed in parentheses.Subquery expressions can be used in the following ways:

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.

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 query returns more columns or rows, a run-time error will occur (“Multiple rows in a singleton select…​”).

A condition — or Boolean expression — is a statement about the data that, like a predicate, can resolve to TRUE, FALSE or NULL.Conditions consist of one or more predicates, possibly negated using NOT and connected by AND and OR operators.Parentheses may be used for grouping predicates and controlling evaluation order.

A predicate may embed other predicates.Evaluation sequence is in the outward direction, i.e. the innermost predicates are evaluated first.Each “level” is evaluated in precedence order until the truth value of the ultimate condition is resolved.

A comparison predicate consists of two expressions connected with a comparison operator.There are six traditional comparison operators:

For the complete list of comparison operators with their variant forms, see Comparison Operators.

If one of the sides (left or right) of a comparison predicate has NULL in it, the value of the predicate will be UNKNOWN.

Other comparison predicates are marked by keyword symbols.

The BETWEEN predicate tests whether a value falls within a specified range of two values.(NOT BETWEEN tests whether the value does not fall within that range.)

The operands for BETWEEN predicate are two arguments of compatible data types.The BETWEEN predicate in Firebird is asymmetrical — if the lower bound is not the first argument, the BETWEEN predicate will return FALSE.The search is inclusive (the values represented by both arguments are included in the search).In other words, the BETWEEN predicate could be rewritten:

When BETWEEN is used in the search conditions of DML queries, the Firebird optimizer can use an index on the searched column, if it is available.

The LIKE predicate compares the character-type expression with the pattern defined in the second expression.Case- or accent-sensitivity for the comparison is determined by the collation that is in use.A collation can be specified for either operand, if required.

Two wildcard symbols are available for use in the search pattern:

If the tested value matches the pattern, taking into account wildcard symbols, the predicate is TRUE.

If the search string contains either of the wildcard symbols, the ESCAPE clause can be used to specify an escape character.The escape character must precede the ‘%’ or ‘_’ symbol in the search string, to indicate that the symbol is to be interpreted as a literal character.

STARTING WITH, CONTAINING, SIMILAR TO

The STARTING WITH predicate searches for a string or a string-like type that starts with the characters in its value argument.The case- and accent-sensitivity of STARTING WITH depends on the collation of the first value.

When STARTING WITH is used in the search conditions of DML queries, the Firebird optimizer can use an index on the searched column, if it exists.

Search for employees whose last names start with “Jo”:

LIKE

The CONTAINING predicate searches for a string or a string-like type looking for the sequence of characters that matches its argument.It can be used for an alphanumeric (string-like) search on numbers and dates.A CONTAINING search is not case-sensitive.However, if an accent-sensitive collation is in use then the search will be accent-sensitive.

LIKE

SIMILAR TO matches a string against an SQL regular expression pattern.Unlike in some other languages, the pattern must match the entire string to succeed — matching a substring is not enough.If any operand is NULL, the result is NULL.Otherwise, the result is TRUE or FALSE.

If a literal pattern is used, and it doesn’t start with a wildcard or other special regex character, SIMILAR TO can use an index.

To match against a character that is special in regular expressions, that character has to be escaped.There is no default escape character;the user specifies one when needed:

The last line demonstrates that the escape character can also escape itself, if needed.

The following syntax defines the SQL regular expression format.It is a complete and correct top-down definition.It is also highly formal and long, and may be daunting to anyone who hasn’t already some experience with regular expressions (or with highly formal, rather long top-down definitions).Feel free to skip it and read the next section, Building Regular Expressions, which uses a bottom-up approach, aimed at the rest of us.

In this section are the elements and rules for building SQL regular expressions.

Within regular expressions, most characters represent themselves.The only exceptions are the special characters below:

... and the escape character, if it is defined.

A regular expression that contains no special characters or escape characters matches only strings that are identical to itself (subject to the collation in use).That is, it functions just like the ‘=’ operator:

The known SQL wildcards ‘_’ and ‘%’ match any single character and a string of any length, respectively:

Notice how ‘%’ also matches the empty string.

A bunch of characters enclosed in brackets define a character class.A character in the string matches a class in the pattern if the character is a member of the class:

As can be seen from the second line, the class only matches a single character, not a sequence.

Within a class definition, two characters connected by a hyphen define a range.A range comprises the two endpoints and all the characters that lie between them in the active collation.Ranges can be placed anywhere in the class definition without special delimiters to keep them apart from the other elements.

The following predefined character classes can also be used in a class definition:

Including a predefined class has the same effect as including all its members.Predefined classes are only allowed within class definitions.If you need to match against a predefined class and nothing more, place an extra pair of brackets around it.

If a class definition starts with a caret, everything that follows is excluded from the class.All other characters match:

If the caret is not placed at the start of the sequence, the class contains everything before the caret, except for the elements that also occur after the caret:

Lastly, the already mentioned wildcard ‘_’ is a character class of its own, matching any single character.

A question mark (‘?’) immediately following a character or class indicates that the preceding item may occur 0 or 1 times to match:

An asterisk (‘*’) immediately following a character or class indicates that the preceding item may occur 0 or more times to match:

A plus sign (‘+’) immediately following a character or class indicates that the preceding item must occur 1 or more times to match:

If a character or class is followed by a number enclosed in braces (‘{’ and ‘}’), it must be repeated exactly that number of times to match:

If the number is followed by a comma (‘,’), the item must be repeated at least that number of times to match:

If the braces contain two numbers separated by a comma, the second number not smaller than the first, then the item must be repeated at least the first number and at most the second number of times to match:

The quantifiers ‘?’, ‘*’ and ‘+’ are shorthand for {0,1}, {0,} and {1,}, respectively.

Regular expression terms can be OR’ed with the ‘|’ operator.A match is made when the argument string matches at least one of the terms:

One or more parts of the regular expression can be grouped into subexpressions (also called subpatterns) by placing them between parentheses (‘(’ and ‘)’).A subexpression is a regular expression in its own right.It can contain all the elements allowed in a regular expression, and can also have quantifiers added to it.

Two operands are considered DISTINCT (different) if they have a different value or if one of them is NULL and the other non-null.They are considered NOT DISTINCT (equal) if they have the same value or if both of them are NULL.

IS [NOT] DISTINCT FROM always returns TRUE or FALSE and never UNKNOWN (NULL) (unknown value).Operators ‘=’ and ‘<>’, conversely, will return UNKNOWN (NULL) if one or both operands are NULL.

[fblangref50-commons-isnotnull], [fblangref50-commons-isnotboolean]

The IS predicate with Boolean literal values checks if the expression on the left side matches the Boolean value on the right side.The expression on the left side must be of type BOOLEAN, otherwise an exception is raised.

The IS [NOT] UNKNOWN is equivalent to IS [NOT] NULL.

[fblangref50-commons-isnotnull]

Since NULL is not a value, these operators are not comparison operators.The IS [NOT] NULL predicate tests that the expression on the left side has a value (IS NOT NULL) or has no value (IS NULL).

Search for sales entries that have no shipment date set for them:

This group of predicates includes those that use subqueries to submit values for all kinds of assertions in search conditions.Existential predicates are so called because they use various methods to test for the existence or non-existence of some condition, returning TRUE if the existence or non-existence is confirmed or FALSE otherwise.

The EXISTS predicate uses a subquery expression as its argument.It returns TRUE if the subquery result contains at least one row, otherwise it returns FALSE.

NOT EXISTS returns FALSE if the subquery result contains at least one row, otherwise it returns TRUE.

The IN predicate tests whether the value of the expression on the left side is present in the set of values specified on the right side.The set of values cannot have more than 65535 items.The IN predicate can be replaced with the following equivalent forms:

When the IN predicate is used in the search conditions of DML queries, the Firebird optimizer can use an index on the searched column, if a suitable one exists.Lists that are known to be constant are pre-evaluated as invariants and cached as a binary search tree, making comparisons faster if the condition needs to be tested for many rows or if the value list is long.

In its second form, the IN predicate tests whether the value of the expression on the left side is present — or not present, if NOT IN is used — in the result of the subquery on the right side.

The subquery must specify only one column, otherwise the error “count of column list and variable list do not match” will occur.

Queries using an IN predicate with a subquery can be replaced with a similar query using the EXISTS predicate.For example, the following query:

can be replaced with a similar one using the EXISTS predicate:

However, a query using NOT IN with a subquery does not always give the same result as its NOT EXISTS counterpart.The reason is that EXISTS always returns TRUE or FALSE, whereas IN returns NULL in one of these two cases:

It is in only these two cases that IN () will return NULL while the EXISTS predicate will return FALSE (“no matching row found”).In a search or, for example, an IF (…​) statement, both results mean “failure”, and it makes no difference to the outcome.

For the same data, NOT IN () will return NULL, while NOT EXISTS will return TRUE, leading to opposite results.

As an example, suppose you have the following query:

Now, assume that the NY celebrities list is not empty and contains at least one NULL birthday.Then for every citizen who does not share his birthday with a NY celebrity, NOT IN will return NULL, because that is what IN does.The search condition is thereby not satisfied and the citizen will be left out of the SELECT result, which is wrong.

For citizens whose birthday does match with a celebrity’s birthday, NOT IN will correctly return FALSE, so they will be left out too, and no rows will be returned.

If the NOT EXISTS form is used:

non-matches will have a NOT EXISTS result of TRUE and their records will be in the result set.

EXISTS

The SINGULAR predicate takes a subquery as its argument and evaluates it as TRUE if the subquery returns exactly one row, otherwise the predicate is evaluated as FALSE.The subquery may list several output columns since the rows are not returned anyway, they are only tested for (singular) existence.For brevity, people usually specify ‘SELECT *’.The SINGULAR predicate can return only two values: TRUE or FALSE.

Find those employees who have only one project.

A quantifier is a logical operator that sets the number of objects for which this condition is true.It is not a numeric quantity, but a logical one that connects the condition with the full set of possible objects.Such predicates are based on logical universal and existential quantifiers that are recognised in formal logic.

In subquery expressions, quantified predicates make it possible to compare separate values with the results of subqueries;they have the following common form:

When the ALL quantifier is used, the predicate is TRUE if every value returned by the subquery satisfies the condition in the predicate of the main query.

Show only those clients whose ratings are higher than the rating of every client in Paris.

The quantifiers ANY and SOME are identical in their behaviour.Both are specified in the SQL standard, and they be used interchangeably to improve the readability of operators.When the ANY or the SOME quantifier is used, the predicate is TRUE if any of the values returned by the subquery satisfies the condition in the predicate of the main query.If the subquery returns no rows at all, the predicate is automatically considered as FALSE.

Show only those clients whose ratings are higher than those of one or more clients in Rome.