Table Column Types

Part:   1   2  3 

This chapter describes table column types for:

• Character strings.
• Bit strings.
• Exact numbers.
• Approximate numbers.
• Date and time values.

Table Column Types for Character Strings

There 4 types of table columns to store character strings: CHAR, VARCHAR, NCHAR and NCHAR VARYING.

"CHARACTER(length) or CHAR(length)" - Character string with fixed length.

"CHARACTER VARYING(length) or VARCHAR(length)" - Character string with varying length.

"NATIONAL CHARACTER(length) or NCHAR(length)" - National character string with fixed length.

"NATIONAL CHARACTER VARYING(length) or NCHAR VARYING(length)" - National character string with varying length.

Examples of character string column types, CharStringColumns.sql:

-- CharStringColumns.sql.sql
-- Copyright (c) 1999 by Dr. Herong Yang
--
CREATE TABLE CharString (
   Line VARCHAR(2),
   A CHAR(8),
   B VARCHAR(8), 
   C NCHAR(8),
   D NCHAR VARYING(8)
   );
INSERT INTO CharString VALUES (
   '1',
   'AAA',
   'BBB', 
   'CCC',
   'DDD'
   );
INSERT INTO CharString VALUES (
   '2',
   'AAAAAA',
   'BBBBBB', 
   'CCCCCC',
   'DDDDDD'
   );
INSERT INTO CharString VALUES (
   '3',
   'AAAAAAAAA',
   'BBBBBBBBB', 
   'CCCCCCCCC',
   'DDDDDDDDD'
   );
SELECT * FROM CharString;
DROP TABLE CharString;

Output:

Line    A       B       C       D
1       AAA     BBB     CCC     DDD
2       AAAAAA  BBBBBB  CCCCCC  DDDDDD
3       AAAAAAAA        BBBBBBBB        CCCCCCCC        DDDDDDDD

Note that:

• Line 3 tells us that data is truncated when the specified size is reached.
• My example could not show any differences among the character string data types.

Table Column Types for Bit Strings

There 2 types of table columns to store bit strings: BIT and BIT VARYING.

"BIT(length)" - Bit string with fixed length.

"BIT VARYING(length)" - Bit string with varying length.

Examples of bit string column types, BitStringColumns.sql:

-- BitStringColumns.sql
-- Copyright (c) 1999 by Dr. Herong Yang
--
CREATE TABLE BitString (
   Line VARCHAR(2),
   A BIT(32),
   B CHAR(4)
--   C BIT VARYING(8) -- not supported by MySQL
   );
INSERT INTO BitString VALUES (
   '1',
   'AAA',
   'AAA'
   );
INSERT INTO BitString VALUES (
   '2',
   X'424242',
   X'424242'
   );
INSERT INTO BitString VALUES (
   '3',
   BINARY(X'434343'),
   BINARY(X'434343')
   );
SELECT * FROM BitString;
DROP TABLE BitString;

Output:

Line    A       B
1       0       AAA
2       127     BBB
3       0       CCC

As you can see in the output, my bit string examples are not working. I don't know why.

(Continued on next part...)

Part:   1   2  3