|
|
|
A literal is an explicit representation of a value. There are three types of literals:
|
|
|
String literals are specified by one or more characters enclosed in single quotes. The default data type for string literals is varchar, but a string literal can be assigned to any character data type or to money or date data type without using a data type conversion function.
To compare a string literal with a non-character data type (A), you must either cast the string literal to the non-character data type A, or cast the non-character data type to the string literal type. Failure to do so causes unexpected results if the non-character data type contains the 'NULL (0) value.
For example, to compare the function X string literal that returns a varchar data type to a byte data type, cast the result of the X function as follows:
SELECT * FROM uid_table
WHERE uid = BYTE(X'010000000000000000000000000000')
or
SELECT * FROM uid_table
WHERE HEX(uid) = '010000000000000000000000000000'
|
|
|
To specify a non-printing character in terminal monitor, use a hex (hexadecimal) constant. Hex constants are specified by an X followed by a single-quoted string composed of (an even number of) alphanumeric characters. For example, the following represents the ASCII string ABC<carriage return>:
X'4142430D'
A = X'41', B = X'42', C = X'43', and carriage return = X'OD'.
|
|
|
To include a single quote inside a string literal, it must be doubled. For example:
'The following letter is quoted: ''A''.'
which is evaluated as:
The following letter is quoted: 'A'.
|
|
|
To specify a Unicode literal value within a non-Unicode command string (for example, in a query entered into the terminal monitor), the Unicode literal notation can be used. A Unicode literal is a sequence of ASCII characters intermixed with escaped sequence of hex digits, all enclosed in quotes and preceded by U&. The escape character \ precedes sets of 4 hex digits that are treated as 2-byte Unicode codepoints and the escape sequence \+ precedes sequences of 6 hex digits that are treated as 4-byte Unicode codepoints with a leading byte of 0. For example:
U&'Hello\1234world\+123456'
In this string, Hello is converted to the equivalent Unicode codepoints, the hex digits 1234 are treated as a 2-byte Unicode codepoint, world is also converted to the equivalent Unicode codepoints, and the hex digits 123456 are treated as a 4-byte Unicode codepoint with a leading byte of 0. The resulting literal is the concatenation of the converted Unicode components.
|
|
|
Numeric literals specify numeric values. There are three types of numeric literals:
A numeric literal can be assigned to any of the numeric data types or the money data type without using an explicit conversion function. The literal is automatically converted to the appropriate data type, if necessary.
By default, the period (.) is displayed to indicate the decimal point. This default can be changed by setting II_DECIMAL. For information about setting II_DECIMAL, see the System Administrator Guide.
Note: If II_DECIMAL is set to comma, be sure that when SQL syntax requires a comma (such as a list of table columns or SQL functions with several parameters), that the comma is followed by a space. For example:
select col1, ifnull(col2, 0), left(col4, 22) from t1:
|
|
|
Integer literals are specified by a sequence of up to 10 digits and an optional sign, in the following format:
[+|-] digit {digit} [e digit]
Integer literals are represented internally as either an integer or a smallint, depending on the value of the literal. If the literal is within the range -32,768 to +32,767, it is represented as a smallint. If its value is within the range -2,147,483,648 to +2,147,483,647 but outside the range of a smallint, it is represented as an integer. Values that exceed the range of integers are represented as decimals.
You can specify integers using a simplified scientific notation, similar to the way floating point values are specified. To specify an exponent, follow the integer value with the letter, e, and the value of the exponent. This notation is useful for specifying large values. For example, to specify 100,000 use the exponential notation as follows:
1e5
|
|
|
Decimal literals are specified as signed or unsigned numbers of 1 to 31 digits that include a decimal point. The precision of a decimal number is the total number of digits, including leading and trailing zeros. The scale of a decimal literal is the total number of digits to the right of the decimal point, including trailing zeros.
Decimal literals that exceed 31 digits are treated as floating point values.
Examples of decimal literals are:
3.
-10.
1234567890.12345
001.100
|
|
|
A floating point literal must be specified using scientific notation. The format is:
[+|-] {digit} [.{digit}] e|E [+|-] {digit}
For example:
2.3e-02
At least one digit must be specified, either before or after the decimal point.
|
|
|
Date/time literals specify ANSI compliant date/time values. There are four types of date/time literals:
Date/time literals can be assigned to the corresponding date/time data type without using an explicit conversion function. The value is coded as a quoted string, but is automatically converted to the appropriate internal value.
|
|
|
Literals of the ANSI date type have the following format:
DATE 'date_value'
Defines a date in the format yyyy-mm-dd.
Note: The II_DATE_FORMAT setting has no impact on the processing of date literals.
Examples:
date '2006-05-29'
date '1998-10-08'
date '2000-11-29'
|
|
|
Literals of the ANSI time type have the following format:
TIME 'time_value'
Defines a time value, which must be in the format hh:mm:ss, optionally followed by .fff (fractions of seconds) and also optionally followed by ±hh:mm, the time zone offset.
Examples:
time '11:11:00'
time '18:05:23.425364'
time '5:23:00-5:00'
|
|
|
Literals of the ANSI timestamp type have the following format:
TIMESTAMP 'timestamp_value'
Consists of a date value and a time value separated by a single space. The time values can contain optional fractions of seconds and/or time zone offsets.
Examples:
timestamp '2006-05-29 10:30:00.000-04:00'
timestamp '1918-11-11 11:11:00'
|
|
|
Literals of the ANSI interval type have the following format:
INTERVAL [sign] 'interval_value' interval_qualifier
Indicates a positive (+) or negative (-).
Consists of a year to month interval value or a day to second interval value.
Format of year to month interval value: year-mm (for example: '25-7')
Format of day to second interval value: dddd... hh:mm:ss[.fffffffff] (for example: '15 5:10:27.4325')
Qualifies the interval as one of the following:
Interval qualifier has the following format:
leading field [TO trailing field]
Valid values for leading field and trailing field in order of precedence are:
YEAR
MONTH
DAY
HOUR
MINUTE
SECOND [(p)].
The leading field cannot have lower precedence than the trailing field.
The precision value on the SECOND field indicates the number of digits allowed in the fractional part of the seconds field.
Examples:
interval'5-7' year to month
interval '-0-11' year to month
interval'+24 12:10:5.1234' day to second
interval '124' year
interval '12' month
interval '18' day
interval '10' hour
interval '34' minute
interval '20.23456789' second (9)
interval '8-11' year to month
interval '12 10' day to hour
interval '12 10:20' day to minute
interval '121 10:15:23.123456' day to second(6)