Background

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

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.


Accepted Identifiers:

Customers5 
dataField
_dataField1
ADGROUP
`tableName~`
`GROUP`

Unaccepted Identifiers:

`5Customers`
5Customers
_dataField!
GROUP

Literals

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

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.


Quoted string

  • "abc"
  • "it's"
  • 'it\'s'
  • '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 (\).


Triple-quoted string

  • """abc"""
  • '''it's'''
  • '''Title:"Boy"'''
  • '''two lines'''
  • '''why\?'''
  • 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.


Raw string

  • R"abc+"
  • r'''abc+'''
  • R"""abc+"""
  • r'f\(abc,(.*),def\)'
  • 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

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

Syntax Options:

[+-]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).


Examples:

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:

  • "NaN"
  • "inf" or "+inf"
  • "-inf"


Case Sensitivity

Keywords: No

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

Reserved Keywords

The following are reserved keywords and cannot be used as identifiers unless escaped with backticks `.

ALL

AND

ANY

ARRAY

AS

ASC

ASSERT_ROWS_MODIFIED

AT

BETWEEN

BY

CASE

CAST

COLLATE

CONTAINS

CREATE

CROSS

CUBE

CURRENT

DEFAULT

DEFINE

DESC

DISTINCT

ELSE

END

ENUM

ESCAPE

EXCEPT

EXCLUDE

EXISTS

EXTRACT

FALSE

FETCH

FOLLOWING

FOR

FROM

FULL

GROUP

GROUPING

GROUPS

HASH

HAVING

IF

IGNORE

IN

INNER

INTERSECT

INTERVAL

INTO

IS

JOIN

LATERAL

LEFT

LIKE

LIMIT

LOOKUP

MERGE

NATURAL

NEW

NO

NOT

NULL

NULLS

OF

ON

OR

ORDER

OUTER

OVER

PARTITION

PRECEDING

PROTO

RANGE

RECURSIVE

RESPECT

RIGHT

ROLLUP

ROWS

SELECT

SET

SOME

STRUCT

TABLESAMPLE

THEN

TO

TREAT

TRUE

UNBOUNDED

UNION

UNNEST

USING

WHEN

WHERE

WINDOW

WITH

WITHIN

Terminating Semicolons

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.


Did this answer your question?