A character string <data type> is defined by a descriptor that contains five pieces of information:

  1. The <data type>'s name: either CHARACTER, CHARACTER VARYING, CHARACTER LARGE OBJECT, NATIONAL CHARACTER, NATIONAL CHARACTER VARYING or NATIONAL CHARACTER LARGE OBJECT.
  2. The <data type>'s fixed length or maximum length (as applicable).
  3. The name of the Character set that the <data type>'s set of valid values belong to. (An operation that attempts to make a character string <data type> contain a character that does not belong to its Character set will fail: your DBMS will return the SQLSTATE error 22021 "data exception-character not in repertoire".)
  4. The name of the <data type>'s default Collation. (This is the Collation that may be used to compare the <data type>'s values in the absence of an explicit COLLATE clause.)
  5. The <data type>'s coercibility attribute: normally IMPLICIT, but can be EXPLICIT. (The coercibility attribute helps your DBMS determine which Collation to use for a comparison that doesn't provide an explicit COLLATE clause.)

CHARACTER

The required syntax for a CHARACTER <data type> specification is:

CHARACTER  ::=
CHARACTER [ (length) ] 
[ CHARACTER SET  ] 
[ COLLATE  ]

CHARACTER may be abbreviated as CHAR and is a fixed length alphanumeric string, exactly "length" characters long. It defines a set of character string values that belong to a given Character set. For example, this <character string literal>:

'BOB'

is a valid value for this <data type> specification:

CHAR(3)

The optional length, if specified, is an unsigned, positive integer that defines the exact length, in characters, of acceptable values. The minimum length and the default length are both 1. For example, these two <data type> specifications:

CHAR

CHAR(1)

both define a set of character string values that are exactly one character long.

[NON-PORTABLE] The maximum length for CHAR is non-standard because the SQL Standard requires implementors to define CHAR's maximum length. FIPS says that CHAR should have a maximum length of at least 1000 characters. [OCELOT Implementation] The OCELOT DBMS that comes with this book allows you to define a length ranging from 1 to 4096 for CHAR.

[Obscure Rule] The optional CHARACTER SET clause names the Character set that the <data type>'s values belong to. Your current <AuthorizationID> must have the USAGE Privilege for that Character set. For example, this <data type> specification:

CHAR(15) CHARACTER SET LATIN1

defines a set of character string values, exactly 15 characters long, that belong to the LATIN1 Character set. If you omit the CHARACTER SET clause when specifying a character string <data type> in a <Column definition or a CREATE DOMAIN statement, the <data type>'s Character set is the Character set named in the DEFAULT CHARACTER SET clause of the CREATE SCHEMA statement that defines the Schema that the <data type> belongs to. For example, consider this SQL statement, which creates a Schema that has a default Character set of LATIN1:

CREATE SCHEMA schema_example 
  DEFAULT CHARACTER SET INFORMATION_SCHEMA.LATIN;

Based on this definition, the <data type> specification in this SQL statement defines a set of character string values, exactly 15 characters long, that belong to the LATIN1 Character set:

   CREATE TABLE schema_example.Table_1 ( 
      column_1 CHAR(15));

[NON-PORTABLE] If you omit the CHARACTER SET clause when specifying a character string <data type> anywhere other than in a <Column definition> or a CREATE DOMAIN statement, the <data type>'s Character set is non-standard because the SQL Standard requires implementors to define a default Character set for such situations. [OCELOT Implementation] The OCELOT DBMS that comes with this book defines ISO8BIT as the default Character set for such situations.

[Obscure Rule] A CHAR <data type> has a coercibility attribute of IMPLICIT. The optional COLLATE clause defines the <data type>'s default Collation. The Collation named must be a Collation defined for the relevant Character set, but you may define a Column, Field or Domain with a default Collation that is different from the default Collation of its Character set. If you're using COLLATE in a SQL-Schema statement, then the <AuthorizationID> that owns the containing Schema must have the USAGE Privilege on "<Collation name>". If you're using COLLATE in any other SQL statement, then your current <AuthorizationID> must have the USAGE Privilege on "<Collation name>". For example, these two <data type> specifications:

   CHAR(15) COLLATE my.collation_1

   CHAR(15) CHARACTER SET my.charset_1 COLLATE my.collation_1

both define a set of character string values exactly 15 characters long. The first example defines the <data type>'s Character set to be the default Character set. The second example defines the <data type>'s Character set to be a Character set named MY.CHARSET_1. Both examples define the <data type>'s default Collation to be a Collation named MY.COLLATION_1. If you omit the COLLATE clause, the <data type> is defined as if its Character set's default Collation was explicitly specified -- see "Character Strings and Collations".

If you want to restrict your code to Core SQL, don't use the CHARACTER SET clause or the COLLATE clause for CHAR <data type> specifications.

NATIONAL CHARACTER

The required syntax for a NATIONAL CHARACTER <data type> specification is:

NATIONAL CHARACTER <data type> ::= NATIONAL CHARACTER [ (length) ] [ COLLATE <Collation name> ]

NATIONAL CHARACTER may be abbreviated as NATIONAL CHAR and as NCHAR. NCHAR is a synonym for a CHAR <data type> that belongs to a predefined "national" Character set.

Other than the fact that you may not add a CHARACTER SET clause to an NCHAR <data type> specification because NCHAR implies the same national Character set used for <national character string literal>s and NCHAR VARYING and NCLOB <data type>s, the specifications for the NCHAR and CHAR <data type>s are the same. Here are two examples of an NCHAR <data type> specification:

   NCHAR(10) 
    -- uses the national Character set's Collation

   NCHAR(10) COLLATE my.collation_1 
    -- uses a Collation named MY.COLLATION_1

[NON-PORTABLE] The national Character set used by <national character string literal>s and the NCHAR, NCHAR VARYING and NCLOB <data type>s is non-standard because the SQL Standard requires implementors to define a national Character set. [OCELOT Implementation] The OCELOT DBMS that comes with this book defines the national Character set to be ISO8BIT. For example, these two <data type> specifications both define the same set of valid values:

   NCHAR(10)
   CHAR(10) CHARACTER SET ISO8BIT

If you want to restrict your code to Core SQL, don't use the NCHAR <data type>.

CHARACTER VARYING

The required syntax for a CHARACTER VARYING <data type> specification is:

CHARACTER VARYING  ::=
CHARACTER VARYING (length) 
[ CHARACTER SET  ] 
[ COLLATE  ]

CHARACTER VARYING may be abbreviated as CHAR VARYING and as VARCHAR and is a variable length alphanumeric string, from zero to "length" characters long. It defines a set of character string values that belong to a given Character set. For example, these three <character string literal>s:

'BOB'
'BOBBY'
'ROBERT'

are all valid values for this <data type> specification:

VARCHAR(6)

The mandatory length specification is an unsigned, positive integer that defines the maximum length, in characters, of acceptable values. The minimum length is 1.

[NON-PORTABLE] The maximum length for VARCHAR is non-standard because the SQL Standard requires implementors to define VARCHAR's maximum length. FIPS says that VARCHAR should have a maximum length of at least 1000 characters. [OCELOT Implementation] The OCELOT DBMS that comes with this book allows you to define a length ranging from 1 to 4096 for VARCHAR.

[Obscure Rule] The optional CHARACTER SET clause names the Character set that the <data type>'s values belong to; see the remarks under "CHARACTER". For example, this <data type> specification:

VARCHAR(15) CHARACTER SET LATIN1

defines a set of character string values, 0 to 15 characters long, that belong to the LATIN1 Character set. (Zero length strings may be stored in a VARCHAR field.)

[Obscure Rule] A VARCHAR <data type> has a coercibility attribute of IMPLICIT. The optional COLLATE clause defines the <data type>'s default Collation; see the remarks under "CHARACTER". For example, these two <data type> specifications:

VARCHAR(15) COLLATE my.collation_1
VARCHAR(15) CHARACTER SET my.charset_1 COLLATE my.collation_1

both define a set of character string values, 0 to 15 characters long, that have a default Collation called MY.COLLATION_1.

If you want to restrict your code to Core SQL, don't use the CHARACTER SET clause or the COLLATE clause for VARCHAR <data type> specifications.

NATIONAL CHARACTER VARYING

The required syntax for a NATIONAL CHARACTER VARYING <data type> specification is:

NATIONAL CHARACTER VARYING  ::=
NATIONAL CHARACTER VARYING (length) [ COLLATE  ]

NATIONAL CHARACTER VARYING may be abbreviated as NATIONAL CHAR VARYING and as NCHAR VARYING. NCHAR VARYING is a synonym for a VARCHAR <data type> that belongs to a predefined "national" Character set.

Other than the fact that you may not add a CHARACTER SET clause to an NCHAR VARYING <data type> specification because NCHAR VARYING implies the same national Character set used for <national character string literal>s and NCHAR and NCLOB <data type>s, the specifications for the NCHAR VARYING and VARCHAR <data type>s are the same. Here are two examples of an NCHAR VARYING <data type> specification:

NCHAR VARYING(10) 
-- uses the national Character set's Collation

NCHAR VARYING(10) COLLATE my.collation_1 
-- uses a Collation named MY.COLLATION_1

[NON-PORTABLE] The national Character set used by <national character string literal>s and the NCHAR, NCHAR VARYING and NCLOB <data type>s is non-standard because the SQL Standard requires implementors to define a national Character set. [OCELOT Implementation] The OCELOT DBMS that comes with this book defines the national Character set to be ISO8BIT.For example, these two <data type> specifications both define the same set of valid values:

NCHAR VARYING(10)
VARCHAR(10) CHARACTER SET ISO8BIT

If you want to restrict your code to Core SQL, don't use the NCHAR VARYING <data type>.

CHARACTER LARGE OBJECT

The required syntax for a CHARACTER LARGE OBJECT <data type> specification is:

CHARACTER LARGE OBJECT  ::=
{CHARACTER LARGE OBJECT | CLOB} [ (length) ] 
[ CHARACTER SET  ] 
[ COLLATE  ]

CHARACTER LARGE OBJECT may be abbreviated as CHAR LARGE OBJECT and as CLOB and is a variable length alphanumeric string, from zero to "length" characters long. It defines a set of large object character string values that belong to a given Character set. For example, these three <character string literal>s:

'BOB'
'BOBBY'
'ROBERT'

are all valid values for this <data type> specification:

CLOB(6)

The optional length, if specified, is an unsigned positive integer, possibly followed by a letter code (either K, M or G), that defines the maximum length, in characters, of acceptable values in the CBLOB> field.

  • The minimum length is 1.
  • The length may include the letter code "K" (kilobyte), "M" (megabyte), or "G" (gigabyte).
  • If the length is defined as nk, the CLOB may hold up to n*1024 characters.
  • If the length is defined as nM, the CLOB may hold up to n*1,048,576 characters.
  • If the length is defined as nG, the CLOB may hold up to n*1,073,741,824 characters.

For example, this <data type> specification defines a set of large object character string values that may range from zero to 20 characters:

CLOB(20)

(Zero length large object character strings can be stored in a CLOB field.)

This <data type> specification defines a set of large object character string values that may range from zero to 2048 characters:

CLOB(2K)

This <data type> specification defines a set of large object character string values that may range from zero to 2,097,152 characters:

CLOB(2M)

And this <data type> specification defines a set of large object character string values that may range from zero to 2,147,483,648 characters:

CLOB(2G)

[NON-PORTABLE] The default length and the maximum length for CLOB are non- standard because the SQL Standard requires implementors to define CLOB's default and maximum lengths. [OCELOT Implementation] The OCELOT DBMS that comes with this book allows the length of CLOB to range from 1 to 32 and sets the default length of a CLOB <data type> to 1K. For example, these two <data type> specifications are equivalent: both define a set of large object character string values that may range from zero to 1024 characters:

CLOB
CLOB(1K)

[Obscure Rule] The optional CHARACTER SET clause names the Character set that the <data type>'s values belong to; see the remarks under "CHARACTER". For example, this <data type> specification:

CLOB(5M) CHARACTER SET LATIN1

defines a set of large object character string values, 0 to 5,242,880 characters long, that belong to the LATIN1 Character set.

[Obscure Rule] A CLOB <data type> has a coercibility attribute of IMPLICIT. The optional COLLATE clause defines the <data type>'s default Collation; see the remarks under "CHARACTER". For example, these two <data type> specifications:

CLOB(3G) COLLATE my.collation_1
CLOB(3G) CHARACTER SET my.charset_1 COLLATE my.collation_1

both define a set of large object character string values, 0 to 3,221,225,472 characters long, that have a default Collation called MY.COLLATION_1.

If you want to restrict your code to Core SQL, don't use the CHARACTER SET clause or the COLLATE clause for CLOB <data type> specifications.

NATIONAL CHARACTER LARGE OBJECT

The required syntax for a NATIONAL CHARACTER LARGE OBJECT <data type> specification is:

NATIONAL CHARACTER LARGE OBJECT  ::=
NATIONAL CHARACTER LARGE OBJECT [ (length) ][ COLLATE  ]

NATIONAL CHARACTER LARGE OBJECT may be abbreviated as NCHAR LARGE OBJECT and as NCLOB. NCLOB is a synonym for a CLOB <data type> that belongs to a predefined "national" Character set.

Other than the fact that you may not add a CHARACTER SET clause to an NCLOB <data type> specification because NCLOB implies the same national Character set used for <national character string literal>s and NCHAR and NCHAR VARYING <data type>s, the specifications for the NCLOB and CLOB <data type>s are the same. Here are two examples of an NCLOB <data type> specification:

NCLOB(2K) 
-- uses the national Character set's Collation

NCLOB(2K) COLLATE my.collation_1 
-- uses a Collation named MY.COLLATION_1

[NON-PORTABLE] The national Character set used by <national character string literal>s and the NCHAR, NCHAR VARYING and NCLOB <data type>s is non-standard because the SQL Standard requires implementors to define a national Character set. [OCELOT Implementation] The OCELOT DBMS that comes with this book defines the national Character set to be ISO8BIT. For example, these two <data type> specifications both define the same set of valid values:

NCLOB(1G)
CLOB(1G) CHARACTER SET ISO8BIT

If you want to restrict your code to Core SQL, don't use the NCLOB <data type>.

Now that we've described SQL's character string <data type>s, let's look at some example SQL statements that put them to use.

These SQL statements make a Table with six fixed length character string Columns, insert a row, then search for any string greater than 'hi'.

CREATE TABLE Fixed_Char_Examples (
      occurrence_char_1 CHAR(2),
      occurrence_char_2 CHAR(2) CHARACTER SET LATIN1,
      occurrence_char_3 CHAR(2) COLLATE my.collation_1,
      occurrence_char_4 CHAR(2) CHARACTER SET LATIN1 COLLATE my.collation_1,
      occurrence_nchar_1 NCHAR(2),
      occurrence_nchar_2 NCHAR(2) COLLATE my.collation_1);

INSERT INTO Fixed_Char_Examples (
      occurrence_char_1,
      occurrence_char_2,
      occurrence_char_3,
      occurrence_char_4,
      occurrence_nchar_1,
      occurrence_nchar_2)
      VALUES ('mm','mm','mm','mm','mm','mm');

SELECT occurrence_char_1,
       occurrence_char_2,
       occurrence_char_3,
       occurrence_char_4,
       occurrence_nchar_1,
       occurrence_nchar_2
FROM   Fixed_Char_Examples
WHERE  occurrence_char_4 > 'hi';

SELECT occurrence_char_1,
       occurrence_char_2,
       occurrence_char_3,
       occurrence_char_4,
       occurrence_nchar_1,
       occurrence_nchar_2
FROM   Fixed_Char_Examples
WHERE  occurrence_nchar_2 > N'hi';

These SQL statements make a Table with six variable length character string Columns, insert a row, then search for any string not equal to 'hi'.

CREATE TABLE Varying_Char_Examples (
      occurrence_varchar_1 VARCHAR(5),
      occurrence_varchar_2 VARCHAR(5) CHARACTER SET LATIN1,
      occurrence_varchar_3 VARCHAR(5) COLLATE my.collation_1,
      occurrence_varchar_4 VARCHAR(5) CHARACTER SET LATIN1 COLLATE my.collation_1,
      occurrence_nvchar_1 NCHAR VARYING(5),
      occurrence_nvchar_2 NCHAR VARYING(5) COLLATE my.collation_1);

INSERT INTO Varying_Char_Examples (
      occurrence_varchar_1,
      occurrence_varchar_2,
      occurrence_varchar_3,
      occurrence_varchar_4,
      occurrence_nvchar_1,
      occurrence_nvchar_2)
      VALUES ('mm','mm','mm','mm','mm','mm');

SELECT occurrence_varchar_1,
       occurrence_varchar_2,
       occurrence_varchar_3,
       occurrence_varchar_4,
       occurrence_nvchar_1,
       occurrence_nvchar_2
FROM   Varying_Char_Examples
WHERE  occurrence_varchar_4 <> 'hi';

SELECT occurrence_varchar_1,
       occurrence_varchar_2,
       occurrence_varchar_3,
       occurrence_varchar_4,
       occurrence_nvchar_1,
       occurrence_nvchar_2
FROM   Varying_Char_Examples
WHERE  occurrence_nvchar_2 <> N'hi';

These SQL statements make a Table with six large object character string Columns, insert a row, then search for any string equal to 'hi'.

   CREATE TABLE Large_Char_Examples (
      occurrence_clob_1 CLOB(10),
      occurrence_clob_2 CLOB(10K) CHARACTER SET LATIN1,
      occurrence_clob_3 CLOB(10M) COLLATE my.collation_1,
      occurrence_clob_4 CLOB(10G) CHARACTER SET LATIN1 COLLATE my.collation_1,
      occurrence_nclob_1 NCLOB(2K),
      occurrence_nclob_2 NCLOB COLLATE my.collation_1);

INSERT INTO Large_Char_Examples (
      occurrence_clob_1,
      occurrence_clob_2,
      occurrence_clob_3,
      occurrence_clob_4,
      occurrence_nclob_1,
      occurrence_nclob_2)
      VALUES ('mm','mm','mm','mm','mm','mm');

SELECT occurrence_clob_1,
       occurrence_clob_2,
       occurrence_clob_3,
       occurrence_clob_4,
       occurrence_nclob_1,
       occurrence_nclob_2
FROM   Large_Char_Examples
WHERE  occurrence_clob_4 = 'hi';

SELECT occurrence_clob_1,
       occurrence_clob_2,
       occurrence_clob_3,
       occurrence_clob_4,
       occurrence_nclob_1,
       occurrence_nclob_2
FROM   Large_Char_Examples
WHERE  occurrence_nclob_2 = N'hi';

Comments

Comments loading...