A SQL statement is made up of identifiers, quoted identifiers, literals, keywords, operators, and special characters. This article specifies how to use each of them in Count.
Identifiers are names that are associated with columns, tables, and other database objects. They can be unquoted or quoted.
- Identifiers can be used in path expressions that return a STRUCT.
- Some identifiers are case-sensitive and some are not. For details, see Case Sensitivity.
- Unquoted identifiers must begin with a letter or an underscore character. Subsequent characters can be letters, numbers, or underscores.
- Quoted identifiers must be enclosed by backtick (`) characters. Quoted identifiers cannot be empty. Quoted identifiers have the same escape sequences as string literals. A reserved keyword must be a quoted identifier if it is a standalone keyword or the first component of a path expression. It may be unquoted as the second or later component of a path expression.
- Table name identifiers have additional syntax to support dashes (-) when referenced in FROM and TABLE clauses.
A literal represents a constant value of a built-in data type. Some, but not all, data types can be expressed as literals.
String literals must be quoted, either with single (') or double (") quotation marks, or triple-quoted with groups of three single (''') or three double (""") quotation marks.
- 'Title: "Boy"'
- Quoted strings enclosed by single (') quotes can contain unescaped double (") quotes, as well as the inverse.
- Backslashes (\) introduce escape sequences. See the Escape Sequences table below.
- Quoted strings cannot contain newlines, even when preceded by a backslash (\).
- '''two lines'''
- Embedded newlines and quotes are allowed without escaping - see fourth example.
- Backslashes (\) introduce escape sequences. See Escape Sequences table below.
- A trailing unescaped backslash (\) at the end of a line is not allowed. End the string with three unescaped quotes in a row that match the starting quotes.
- Quoted or triple-quoted literals that have the raw string literal prefix (r or R) are interpreted as raw/regex strings.
- Backslash characters (\) do not act as escape characters. If a backslash followed by another character occurs inside the string literal, both characters are preserved.
- A raw string cannot end with an odd number of backslashes. Raw strings are useful for constructing regular expressions.
The table here lists all valid escape sequences for representing non-alphanumeric characters in string literals. Any sequence not in this table produces an error.
Integer literals are either a sequence of decimal digits (0–9) or a hexadecimal value that is prefixed with "0x" or "0X". Integers can be prefixed by "+" or "-" to represent positive and negative values, respectively. Examples:
123 0xABC -123
An integer literal is interpreted as an INT64.
Floating Point Literals
[+-]DIGITS.[DIGITS][e[+-]DIGITS] [DIGITS].DIGITS[e[+-]DIGITS] DIGITSe[+-]DIGITS
DIGITS represents one or more decimal numbers (0 through 9) and e represents the exponent marker (e or E).
123.456e-67 .1E4 58. 4e2
Numeric literals that contain either a decimal point or an exponent marker are presumed to be type double. Implicit coercion of floating-point literals to float type is possible if the value is within the valid float range. There is no literal representation of NaN or infinity, but the following case-insensitive strings can be explicitly cast to float:
- "inf" or "+inf"
Built-in Function names: No
Table names: Yes
Column names: No
String values: Yes
String comparisons: Yes
Aliases within a query: No
Regular expression matching: case sensitive by default, unless the expression itself specifies that it should be case insensitive.
LIKE matching: Yes
The following are reserved keywords and cannot be used as identifiers unless escaped with backticks
You can optionally use a terminating semicolon (;) when you submit a query string statement through an Application Programming Interface (API).
You can only enter one statement per cell.