Literals represent constants, which possess a specific value as well as a corresponding data type. Literals can be entered into tables as values or used within queries as constants, for example, in comparisons or function parameters. If the literal displays a different data type to the assigned or comparison type, the data type which is smaller is converted implicitly.
You can group Literals as follows:
- Numeric Literals
- Boolean Literals
- Date/Time Literals
- Interval Literals
- String Literals
- NULL Literals
There is also a special literal for the NULL value.
Examples
| Literals | Meaning |
|---|---|
| 123 |
Integer number (integral decimal number) |
| -123.456 |
Decimal number |
| 1.23456E-32 |
Double value |
| TRUE |
Boolean value |
| DATE '2007-03-31' |
Date |
| TIMESTAMP '2007-03-31 12:59:30.123' |
Timestamp |
| INTERVAL '13-03' YEAR TO MONTH |
Interval (YEAR TO MONTH ) |
| INTERVAL '1 12:00:30.123' DAY TO SECOND |
Interval (DAY TO SECOND) |
| 'ABC' |
String |
| NULL |
NULL value |
Numeric Literals
Numeric literals represent a number. If the number is represented exactly in a DECIMAL type, Exasol chooses the smallest literal type. Otherwise the type of the literal is DOUBLE.
Numeric literals can be distinguished as follows:
integer_literal::=
decimal_literal::=
double_literal::=
Boolean Literals
There are only three valid values for Boolean literals: TRUE, FALSE, and UNKNOWN (which is similar to NULL). Instead of a literal, you can also use strings (for example, 'T' or 'True') or numbers (0 and 1). For more details, refer to Boolean data type section.
boolean_literal::=
Date/Time Literals
Date/Time literals represent a particular point in time. They have a fixed format, and therefore, they are not as flexible as the TO_DATE or TO_TIMESTAMP conversion functions. In most cases, these functions should be given priority over literals. For more information refer to Built-in functions and Data/Time format models sections.
date_literal::=
timestamp_literal::=
The following formats are specified for string:
| Type | Format | Explanation |
|---|---|---|
| DATE | 'YYYY-MM-DD' | Fixed format for DATE literals ('Year-Month-Day') |
| TIMESTAMP | 'YYYY-MM-DD HH:MI:SS.FF3' | Fixed format for TIMESTAMP literals ('Year-Month- Day Hours-Minutes-Seconds. Milliseconds') |
Interval Literals
Interval literals describe an interval of time and are very useful for datetime arithmetic.
interval_year_to_month_literal::=
Where:
- int[-int] defines integers values for the number of years and months. In case of YEAR TO MONTH you have to specify int-int
- The optional parameter precision (1-9) specifies the maximal number of digits. Without this parameter two digits are allowed (months from 0 to 11)
Examples
| Interval Literals | Meaning |
|---|---|
| INTERVAL '5' MONTH | Five months |
| INTERVAL '130' MONTH (3) | 130 months |
| INTERVAL '27' YEAR | 27 years |
| INTERVAL '2-1' YEAR TO MONTH | Two years and one month |
| INTERVAL '100-1' YEAR(3) TO MONTH | 100 years and one month |
interval_day_to_second_literal::=
Where:
- int defines the number of days (see Precision for maximum number of digits)
- time_expr specifies a time value in format HH[:MI[:SS[.n]]] or MI[:SS[.n]] or SS[.n]. Valid values are 0-23 for hours (HH), 0-59 for minutes (MI) and 0-59.999 for seconds (SS). The parameter precision defines the maximal number of digits for the leading field which allows you also to use larger numbers for hours, minutes and seconds as shown in the example table below. The parameter fractional_precision defines at which position the fractional part of the seconds are rounded (0-9, default is 3). In time_expr, you have to specify the whole used range, for example, HH:MI in case of HOUR TO MINUTE.
- The precision of seconds is limited to 3 decimal places (similar to timestamp values), although you can specify more digits in the literal.
- The optional parameter precision(1-9) specifies the maximum number of digits for the leading field. By default, two digits are allowed.
- The interval borders must be descending. For example, SECOND TO MINUTE is not valid.
Example
| Interval Literals | Meaning |
|---|---|
| INTERVAL '5' DAY | Five days |
| INTERVAL '100' HOUR(3) | 100 hours |
| INTERVAL '6' MINUTE | Six minutes |
| INTERVAL '1.99999' SECOND(2,2) | 2.00 seconds (rounded after the second place) |
| INTERVAL '10:20' HOUR TO MINUTE | Ten hours and 20 minutes |
| INTERVAL '2 23:10:59' DAY TO SECOND | Two days, 23 hours, 10 minutes and 59 seconds |
| INTERVAL '23:10:59.123' HOUR(2) TO SECOND(3) | 23 hours, 10 minutes and 59.123 seconds |
String Literals
String literals are used to represent text. String literals use the smallest character sets available. The ASCII character set will be used if only ASCII characters are included, else the UTF-8 character set will be used. See String data types for more information.
string_literal::=
Note: An empty string literal ('') is evaluated as NULL value.
To use single quotes within a string, two single quotes are written next to one another. For example, the literal 'AB''C' represents the value AB'C.
