Data Types and Literals

Part:   1  2  3  4  

(Continued from previous part...)

Example 2 - HexStringLiterals.sql:

-- HexStringLiterals.sql
-- Copyright (c) 1999 by Dr. Herong Yang
--
SELECT x'41424344' AS LINE_1;
SELECT x'31323334' AS LINE_2;
SELECT x'31323334' + 0 AS LINE_3;
SELECT x'01' + 0 AS LINE_4;
SELECT x'0001' + 0 AS LINE_5;
SELECT x'ff' + 0 AS LINE_6;
SELECT x'ffffffff' + 0 AS LINE_7;
SELECT x'ffffffffffffffff' + 0 AS LINE_8;

Output

LINE_1
ABCD
LINE_2
1234
LINE_3
825373492
LINE_4
1
LINE_5
1
LINE_6
255
LINE_7
4294967295
LINE_8
-1

Notes:

• Line 1 and 2 tell us that hex string literals are evaluated into character strings in a string expression.
• Line 3, 4, 5, 6 and 7 tell us that hex string literals are evaluated into numbers in numeric expression.
• Line 8 tells us that the binary representation of an integer is 8 bytes. The value of the first bit on the left marks the integer to be negative.

Example 3 - NumericLiterals.sql:

-- NumericLiterals.sql
-- Copyright (c) 1999 by Dr. Herong Yang
--
SELECT 1 AS LINE_1;
SELECT -2 AS LINE_2;
SELECT 3.3 AS LINE_3;
SELECT -4.4e+4 AS LINE_4;
SELECT 12345678901234567890 AS LINE_5;
SELECT 0.12345678901234567890 AS LINE_6;
SELECT 1234567890.1234567890 AS LINE_7;
SELECT 12345678901234567890.1234567890 AS LINE_8;
SELECT 1234567890.1234567890e+10 AS LINE_9;
SELECT 0.0000000000000000000012345678901234567890 AS LINE_10;
SELECT 1.0e+1234567890 AS LINE_11;

Output:

LINE_1
1
LINE_2
-2
LINE_3
3.3
LINE_4
-44000
LINE_5
123456789012345680000000000000
LINE_6
0.12345678901234568000
LINE_7
1234567890.1234567000
LINE_8
12345678901234567000.0000000000
LINE_9
1.2345678901235e+019
LINE_10
1.2345678901235e-021
LINE_11
1.#INF

A number of interesting notes here:

• Line 4 tells us that an approximate numeric literal could be evaluated into an integer.
• Line 5, 6, 7 and 8 tell us that exact numeric literals are evaluated upto about 17 digits.
• Line 9 tells us that an approximate numeric literal is evaluated upto about 15 digits.
• Line 10 tells us that an exact numeric literal could be converted into an proximate numeric value.
• Line 11 tells us that if the internal storage value limit is reached, an approximate numeric literal is evaluated to "1.#INF".

Part:   1  2  3  4