|
|
|
|
|
|
Literals
Literal, i.e. fixed data, values may be given for any of the data types supported in SQL statements, wherever the term literal appears in the syntax diagrams.
String Literals
String literals may be represented in four ways: as character-string-literals, national-character-string-literals, unicode-character-string-literal or hexadecimal-string-literals.
- Character-string-literal
A character-string-literal consists of a sequence of characters enclosed in string delimiters. The standard string delimiter is the single quotation mark: '. Two consecutive single quotation marks within a string are interpreted as a single quotation mark.
- Note: An empty string (i.e. '') is a defined value. It is not NULL.
Only characters in the ISO 8859-1 character set (LATIN1) may be included in a character-string-literal.
- National-character-string-literal
A national-character-string-literal consists of a sequence of Unicode characters enclosed in string delimiters and preceded by the letter N. The standard string delimiter is the single quotation mark: '. Two consecutive single quotation marks within a string are interpreted as a single quotation mark. The case of the preceding N is irrelevant.
- Note: An empty string (i.e. N'') is a defined value. It is not NULL.
- Unicode-string-literal
A unicode-string-literal is used in order to facilitate the specification of Unicode characters in an ASCII environment. It consists of a sequence of Unicode characters enclosed in string delimiters and preceded by the letter U and an ampersand, i.e. U&. The standard string delimiter is the single quotation mark: '. Two consecutive single quotation marks within a string are interpreted as a single quotation mark. Unicode characters are given by four hexadecimal digits preceded by a backslash character (\) or, by six hexadecimal digits preceeded with a backslash character and a plus character. Two consecutive backslash characters within a string are interpreted as a single backslash character. The case of the preceding U is irrelevant.
- Note: An empty string (i.e. U&'') is a defined value. It is not NULL.
- Hexadecimal-string-literal
A hexadecimal-string-literal is a string specified as a sequence of hexadecimal values, enclosed in single quotation marks and preceded by the letter X. The sequence of values must contain an even number of positions (every character in the string literal is represented by a two-position value), and may not contain any characters other than the digits 0-9 and the letters A-F. The case of letters (and of the preceding X) is irrelevant. The code values for characters are those which apply in the host system.
Character Separators
For character, national-character, unicode and hexadecimal-string-literals, you can use a separator within the literal to join two or more substrings. Separators are described in Characters.
This is particularly useful when a string literal extends over more than one physical line, or when control codes are to be combined with character sequences.
Examples
ASCII codes are used for the hexadecimal literals:
String
Value
Note: Since a hexadecimal-string is of type binary, an explicit CAST is required when using a hexadecimal-string as character data. For CAST information, see Assignments.
Numerical Integer Literals
A numerical integer literal is a signed or unsigned number that does not include a decimal point. The sign is a plus (+) or minus (-) sign immediately preceding the first digit.
In determining the precision of an integer literal, leading zeros are significant (i.e. the literal 007 has precision 3).
Examples:
47 -125 +006 0Numerical Decimal Literals
A numerical decimal literal is a signed or unsigned number containing exactly one decimal point.
In determining the precision and scale of a decimal literal, both leading and trailing zeros are significant (i.e. the literal 003.1400 has precision 7, scale 4).
Examples:
4.7 -3. +012.067 0.0 .370Numerical Floating Point Literals
Floating point literals are represented in exponential notation, with a signed or unsigned integer or decimal mantissa, followed by an letter E, followed in turn by a signed or unsigned integer exponent.
The base for the exponent is always 10. The exponent zero may be used. The case of the letter E is irrelevant.
In determining the precision of a floating point literal, leading zeros in the mantissa are significant (i.e. the literal 007E4 has precision 3).
Examples:
1.3E5 means 130000 -4e-2 means -0.04 +03.3E2 means 330 0E+45 means 0 1.53E00 means 1.53DATE, TIME and TIMESTAMP Literals
A literal that represents a DATE, TIME or TIMESTAMP value consists of the corresponding keyword shown below, followed by text enclosed in single quotes ('').
The following formats are allowed:
TIMESTAMP 'date-value <space> time-value'
A date-value has the following format:
year-value - month-value - day-valueA time-value has the following format:
hour-value : minute-value : second-valuewhere second-value has the following format:
whole-seconds-value [. fractional-seconds-value]The year-value, month-value, day-value, hour-value, minute-value, whole-seconds-value and fractional-seconds-value are all unsigned integers.
A year-value contains exactly 4 digits, a fractional-seconds-value may contain up to 9 digits and all the other components each contain exactly 2 digits.
Examples:
TIMESTAMP '1998-11-05 19:20:23.4567'
TIMESTAMP '2003-12-31 23:59:30'
Interval Literals
An interval literal represents an interval value and consists of the keyword INTERVAL followed by text enclosed in single quotes, in the following format:
INTERVAL '[+|-]interval-value' interval-qualifier
The interval-value text must be a valid representation of a value compatible with the INTERVAL data type specified by the interval-qualifier, see Interval Qualifiers.
- If the interval precision includes the YEAR and MONTH fields, the values of these fields should be separated by a minus sign.
- If the interval precision includes the DAY and HOUR fields, the values of these fields should be separated by a space.
- If the interval precision includes the HOUR fields and another field of lower significance (MINUTE and/or SECOND), the values of these fields should be separated by a colon.
- All fields may contain up to 2 digits except that:
- The number of digits in the most significant field must not exceed the leading precision defined by the interval-qualifier. If a leading precision is not explicitly specified in the interval-qualifier, the default (2) applies.
- The SECOND field may have a fractional part, whose maximum length is defined by the interval-qualifier.
Examples:
INTERVAL '1:30' HOUR TO MINUTE
INTERVAL '1000 10:20:30.123' DAY(4) TO SECOND(3)
INTERVAL '-199' YEAR(3) **evaluates to -199
INTERVAL '199' YEAR **Invalid : default leading precision is 2
INTERVAL '5.555' SECOND(1,2) **evaluates to 5.55
INTERVAL '-5.555' SECOND(1,2) **evaluates to -5.55
INTERVAL '19 23' DAY TO MINUTE **Invalid : no minutes in literal
Binary Literals
A binary literal represents an binary value, and is specified as a hexadecimal string, i.e. a string specified as a sequence of hexadecimal values, enclosed in single quotation marks and preceded by the letter X. The sequence of values must contain an even number of positions (every character in the string literal is represented by a two-position value), and may not contain any characters other than the digits 0-9 and the letters A-F. The case of letters (and of the preceding X) is irrelevant. The code values for characters are those which apply in the host system.
Examples:
Standard Compliance
This section summarizes standard compliance concerning literals.
 Mimer Information Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 info@mimer.se |
|
|
|
|
|
|