Syntax

Literals can be:

- [String](#string)
- [Numeric](#numeric)
- [Compound](#compound)
- [`NULL`](#null)
- [Heredocs](#heredoc) (custom string literals)

We take a look at each of these in more detail in the sections below.

### String[​](#string "Direct link to String")

String literals must be enclosed in single quotes. Double quotes are not supported.

Escaping works by either:

- using a preceding single quote where the single-quote character `'` (and only this character) can be escaped as `''`, or
- using the preceding backslash with the following supported escape sequences listed in the table below.

Note

The backslash loses its special meaning i.e. it is interpreted literally should it precede characters other than the ones listed below.

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Supported Escape Description|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `\xHH` 8-bit character specification followed by any number of hex digits (H).|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `\N` reserved, does nothing (eg `SELECT 'a\Nb'` returns `ab`)| `\a` alert|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `\b` backspace|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `\e` escape character|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `\f` form feed|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `\n` line feed|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `\r` carriage return|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `\t` horizontal tab|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `\v` vertical tab|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `\0` null character|  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `\\` backslash|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `\'` (or `''`) single quote|  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `\"` double quote|  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | | `` ` `` backtick|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | `\/` forward slash|  |  |  |  | | --- | --- | --- | --- | | `\=` equal sign|  |  | | --- | --- | | ASCII control characters (c <= 31).  | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |

Note

In string literals, you need to escape at least `'` and `\` using escape codes `\'` (or: `''`) and `\\`.

### Numeric[​](#numeric "Direct link to Numeric")

Numeric literals are parsed as follows:

- If the literal is prefixed with a minus sign `-`, the token is skipped and the result is negated after parsing.
- The numeric literal is first parsed as a 64-bit unsigned integer, using the [strtoull](https://en.cppreference.com/w/cpp/string/byte/strtoul) function.
  - If the value is prefixed with `0b` or `0x`/`0X`, the number is parsed as binary or hexadecimal, respectively.
  - If the value is negative and the absolute magnitude is greater than 263, an error is returned.
- If unsuccessful, the value is next parsed as a floating-point number using the [strtod](https://en.cppreference.com/w/cpp/string/byte/strtof) function.
- Otherwise, an error is returned.

Literal values are cast to the smallest type that the value fits in.
For example:

- `1` is parsed as `UInt8`
- `256` is parsed as `UInt16`.

Important

Integer values wider than 64-bit (`UInt128`, `Int128`, `UInt256`, `Int256`) must be cast to a larger type to parse properly:

```
-170141183460469231731687303715884105728::Int128
340282366920938463463374607431768211455::UInt128
-57896044618658097711785492504343953926634992332820282019728792003956564819968::Int256
115792089237316195423570985008687907853269984665640564039457584007913129639935::UInt256
```

This bypasses the above algorithm and parses the integer with a routine that supports arbitrary precision.

Otherwise, the literal will be parsed as a floating-point number and thus subject to loss of precision due to truncation.

For more information, see [Data types](/docs/sql-reference/data-types).

Underscores `_` inside numeric literals are ignored and can be used for better readability.

The following Numeric literals are supported:

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Numeric Literal Examples|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | **Integers** `1`, `10_000_000`, `18446744073709551615`, `01`| **Decimals** `0.1`| **Exponential notation** `1e100`, `-1e-100`| **Floating point numbers** `123.456`, `inf`, `nan`| **Hex** `0xc0fe`| **SQL Standard compatible hex string** `x'c0fe'`| **Binary** `0b1101`| **SQL Standard compatible binary string** `b'1101'` | | | | | | | | | | | | | | | | | |

Note

Octal literals are not supported to avoid accidental errors in interpretation.

### Compound[​](#compound "Direct link to Compound")

Arrays are constructed with `[]`: `[1, 2, 3]`. Tuples are constructed with `()`: `(1, 'Hello, world!', 2)`.
Technically these are not literals, but expressions with the array creation operator and the tuple creation operator, respectively.
An array must consist of at least one item, and a tuple must have at least two items.

Note

There is a separate case when tuples appear in the `IN` clause of a `SELECT` query.
Query results can include tuples, but tuples cannot be saved to a database (except for tables using the [Memory](/docs/engines/table-engines/special/memory) engine).

### NULL[​](#null "Direct link to NULL")

`NULL` is used to indicate that a value is missing.
To store `NULL` in a table field, it must be of the [Nullable](/docs/sql-reference/data-types/nullable) type.

Note

The following should be noted for `NULL`: