FirebirdSQL logo
  • Anglaiskeyboard_arrow_down
 Data Types and SubtypesData Definition (DDL) Statements 

This chapter covers the elements that are common throughout the implementation of the SQL language — the expressions that are used to extract and operate on conditions about data and the predicates that test the truth of those assertions.

Expressions

SQL expressions provide formal methods for evaluating, transforming and comparing values.SQL expressions may include table columns, variables, constants, literals, various statements and predicates and also other expressions.The complete list of possible tokens in expressions follows.

Description of Expression Elements
Column name

Identifier of a column from a specified table used in evaluations or as a search condition.A column of the array type cannot be an element in an expression except when used with the IS [NOT] NULL predicate.

Array element

An expression may contain a reference to an array member i.e., <array_name>[s], where s is the subscript of the member in the array <array_name>

Arithmetic operators

The +, -, *, / characters used to calculate values

Concatenation operator

The || (“double-pipe”) operator used to concatenate strings

Logical operators

The reserved words NOT, AND and OR, used to combine simple search conditions to create complex conditions

Comparison operators

The symbols =, <>, !=, ~=, ^=, <, <=, >, >=, !<, ~<, ^<, !>, ~> and ^>

Comparison predicates

[fblangref50-commons-predlike], [fblangref50-commons-predstartwith], [fblangref50-commons-predcontaining], [fblangref50-commons-predsimilarto], [fblangref50-commons-predbetween], [fblangref50-commons-isnotnull], IS [NOT] {TRUE | FALSE | UNKNOWN} and [fblangref50-commons-isnotdistinct]

Existential predicates

Predicates used to check the existence of values in a set.The IN predicate can be used both with sets of comma-separated constants and with subqueries that return a single column.The [fblangref50-commons-exists], [fblangref50-commons-singular], [fblangref50-commons-quant-all], [fblangref50-commons-quant-anysome] predicates can be used only with sub-queries.

Constant or Literal

Numbers, or string literals enclosed in apostrophes or Q-strings, Boolean values TRUE, FALSE and UNKOWN, NULL

Datetime literal

An expression, similar to a string literal enclosed in apostrophes, that can be interpreted as a date, time or timestamp value.Datetime literals can be strings of characters and numerals, such as TIMESTAMP '25.12.2016 15:30:35', that can be resolved as datetime value.

Datetime mnemonics

A string literal with a description of a desired datetime value that can be cast to a datetime type.For example 'TODAY', 'NOW'.

Context variable

An internally-defined context variable

Local variable

Declared local variable, input or output parameter of a PSQL module (stored procedure, stored function, trigger, or unnamed PSQL block in DSQL)

Positional parameter

A member of an ordered group of one or more unnamed parameters passed to a stored procedure or prepared query

Subquery

A SELECT statement enclosed in parentheses that returns a single (scalar) value or, when used in existential predicates, a set of values

Function identifier

The identifier of an internal, packaged, stored or external function in a function expression

Type cast

An expression explicitly converting data of one data type to another using the CAST function (CAST (<value> AS <datatype>)).For datetime literals only, the shorthand syntax <datatype> <value> is also supported (DATE '2016-12-25').

Conditional expression

Expressions using CASE and related internal functions

Parentheses

Bracket pairs (…​) used to group expressions.Operations inside the parentheses are performed before operations outside them.When nested parentheses are used, the most deeply nested expressions are evaluated first and then the evaluations move outward through the levels of nesting.

COLLATE clause

Clause applied to CHAR and VARCHAR types to specify the character-set-specific collation to use in string comparisons

NEXT VALUE FOR sequence

Expression for obtaining the next value of a specified generator (sequence).The internal function GEN_ID() does the same.

AT expression

Expression to change the time zone of a datetime.

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.

String Literals

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:

Character String Literal Syntax
<char-literal> ::=
  [<introducer> charset-name] <quote> [<char>...] <quote>
  [{ <separator> <quote> [<char>...] <quote> }... ]

<separator> ::=
  { <comment> | <white space> }

<introducer> ::= underscore (U+005F)
<quote> ::= apostrophe (U+0027)
<char> ::= character representation;
           apostrophe is escaped by doubling
Note

  • Double quotes are not valid for quoting strings.The SQL standard reserves double quotes for a different purpose: delimiting or quoting identifiers.

  • 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'.Or use the alternative quote literal: q'{Mother O'Reilly's home-made hooch}'

  • Care should be taken with the string length if the value is to be written to a CHAR or VARCHAR column.The maximum length for a CHAR or VARCHAR literal 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.

Examples
-- Literal containing single quote
select 'O''Reilly' from RDB$DATABASE;
-- output: O'Reilly
-- whitespace between literal
select 'ab'
       'cd'
from RDB$DATABASE;
-- output: abcd
-- comment and whitespace between literal
select 'ab' /* comment */ 'cd'
from RDB$DATABASE;
-- output: abcd