Data Types and Literals

Part:   1  2  3   4 

(Continued from previous part...)

5. Time Interval Literals are used to construct time intervals with different time units.

• A time interval literal is written in the form of "INTERVAL 'n' unit", where unit can be YEAR, MONTH, DAY, HOUR, MINUTE, or SECOND.

Examples of time interval literals:

INTERVAL '3' YEAR
INTERVAL '1' DAY
INTERVAL '4' MONTH
INTERVAL '1' HOUR
INTERVAL '5' MINUTE
INTERVAL '9' SECOND

6. Null Literal is used to construct a null value for any data type. The syntax of null literal is the word "null".

Data Literal Evaluation

Data literal evaluation is the process converting a data literal to a particular value of a particular data type. This data type must match the type of the expression where the literal is located. Expressions will be reviewed in the next chapter. Now let's look at some of the data literal evaluation rules for each type of data literals.

1. Character String Literals can be evaluated character strings, exact numbers, approximate numbers and data and time values. The evaluation rules are:

• A character string literal will be evaluated to a character string in a string expression. The evaluation is straight forward.
• A character string literal will be evaluated to an exact number or an approximate number in a numeric expression, if the character string literal is also a valid numeric literal without the quoting characters. Otherwise, it will be evaluated to 0. For example, '123' will be evaluated to 123 in numeric contexts. But 'ABC' will be evaluated to 0.
• A character string literal will be evaluated to a date and time value in a date and time expression, if the character string can be matched to the date and time pattern: "yyyy-mm-dd hh:mm:ss.nnn".

2. Hex String Literals can be evaluated to character strings and exact numbers. The evaluation rules are:

• A hex string literal will be evaluated to a character string in a string expression. The hex string literal will be used as an array of binary representation of encoded characters, and decoded back to a character string. For example, x'41424344' will be evaluated to character string "ABCD".
• A hex string literal will be evaluated to an integer in a numeric expression. The hex string literal will be used to match the binary representation of an integer. The literal will be evaluated to the matching integer. For example, x'ffffffffffffffff' will be evaluated to integer -1.

3. Numeric Literals can be evaluated to exact numbers and approximate numbers. The evaluation rules are:

• A numeric literal will be evaluated to an exact number or an approximate number in a numeric expression. The evaluation is straight forward.

Data Literal Evaluation Examples

Example 1 - CharStringLiterals.sql:

-- CharStringLiterals.sql
-- Copyright (c) 1999 by Dr. Herong Yang
--
SELECT 'Hello world!' AS LINE_1;
SELECT 'Loews L''Enfant Plaza' AS LINE_2;
SELECT '123'+ 0 AS LINE_3;
SELECT '0.123e-1' + 0 AS LINE_4;
SELECT 'ABC' + 1 AS LINE_5;

Output on MySQL server:

LINE_1
Hello world!
LINE_2
Loews L'Enfant Plaza
LINE_3
123
LINE_4
0.0123
LINE_5
1

Notes:

• Line 2 shows that "''" is evaluated as "'" in a string expression.
• Line 4 confirms that a character string literal can be evaluated to an approximate number in a numeric expression.
• Line 5 confirms that a character string literal will be evaluated to 0, if the string is an invalid numeric literal.

(Continued on next part...)

Part:   1  2  3   4