An SQL <character string literal> has five parts.
1. Its value — the sequence of characters that make up the <literal>.
2. Its length — the number of characters that make up the <literal>.
3. The name of the Character set that the <literal> belongs to.
4. The name of the <literal>'s default Collation. (This is the Collation that may be used to compare the <literal> with another character string in the absence of an explicit COLLATE clause.)
5. The <literal>'s coercibility attribute — normally COERCIBLE, 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.)
A <character string literal> is either a <character string literal> or a national <character string literal>.
<character string literal>
The required syntax for a <character string literal> is as follows.
<character string literal> ::=
[ _<Character set name> ]'string' [ COLLATE <Collation name> ]
A <character string literal> is a string of zero or more alphanumeric characters inside a pair of single quote marks. The string's characters must all belong to the same Character set. Its <data type> is fixed length CHARACTER, though it is compatible with the CHARACTER,
CHARACTER VARYING, CHARACTER LARGE OBJECT, NATIONAL CHARACTER, NATIONAL CHARACTER VARYING, and NATIONAL CHARACTER LARGE OBJECT <data type>s. The <literal>'s length is the number of characters inside the quote marks; the delimiting single quotes aren't part of the <literal>, so they're not included in the calculation of the
<character string literal>'s size. Two consecutive single quotes within a character string (i.e., ") represent one single quote mark; together, they count as one character when calculating the size of the <literal>. Here is an example of a <character string literal>:
'This is a <character string literal>'
[Obscure Rule] The optional Character set specification — an underscore character immediately preceding a <Character set name> (no space is allowed between them) — names the Character set to which the <literal> belongs. Your current <AuthorizationID> must have the USAGE Privilege for that Character set. For example, this <character string literal>:
_LATIN1 'Hello'
belongs to the LATIN1 Character set. Note: For qualified names, the underscore character always precedes the highest level of explicit qualification in the <Character set name>. If you omit the Character set specification, the characters in the <literal> must belong to the Charac-soft
Page 119
ter set of the SQL-client Module that contains the <literal>. Here are two examples of a
<character string literal>:
'This is a string in the default Character set' _LATIN1'This is a string in the LATIN1 Character set
[Obscure Rule] A <character string literal> normally has a coercibility attribute of COERCIBLE and a default Collation that is the Collation defined for its Character set — See "Character
Strings and Collations" on page 159. The optional COLLATE clause names the <literal>'s EXPLICIT Collation for an operation. The Collation named must be a Collation defined for the relevant Character set, but you may specify a default Collation for a <literal> that is different from the default Collation of its Character set. If you're using COLLATE in an 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>. Here are four more examples of a <character string literal>:
'This string in the default Character set will use the default Character set''s Collation'
_LATIN1'This in the LATIN1 Character set will use LATIN1''s Collation' 'This string in the default Character set will use a Collation named MY.COLLATION_1' COLLATE my.collation_1
_LATIN1'This string in the LATIN1 Character set will use a Collation named MY.COLLATION_1' COLLATE my.collation_1
[Obscure Rule] SQL allows you to break a long <character string literal> into two or more smaller <character string literal>s, split by a <separator> that includes a newline character. When it sees such a <literal>, your DBMS will ignore the <separator> and treat the multiple strings as a single <literal>. For example, here are two equivalent <character string literal>s:
'This is part of a string' ' and this is the other part'
'This is part of a string and this is the order part'
(In the first example, there is a carriage return newline <separator> between string' and and.)
If you want to restrict your code to Core SQL, don't add a Character set specification to
<character string literal>s, don't add a COLLATE clause to <character string literal>s, and don't split long <character string literal>s into smaller strings.
<national character string literal>
The required syntax for a <national character string literal> is as follows.break
<national character string literal> ::=
N'string' [ COLLATE <Collation name> ]
Page 120
A <national character string literal> is a <character string literal> preceded by the letter N; it is a synonym for a <character string literal> that belongs to a predefined ''national" Character set. Its
<data type> is fixed length NATIONAL CHARACTER, though it is compatible with the CHARACTER, CHARACTER VARYING, CHARACTER LARGE OBJECT, NATIONAL CHARACTER, NATIONAL CHARACTER VARYING, and NATIONAL CHARACTER LARGE OBJECT <data type>s.
Other than the fact that you may not add a Character set specification to a <national character string literal> because N'string' implies the same national Character set used for NCHAR, NCHAR VARYING, and NCLOB <data type>s, the specifications for the two types of <character string literal>s are the same. Here are two examples of a <national character string literal>:
N'This string in the national Character set will use the national Character set''s Collation'
N'This string in the national Character set will use a Collation named MY.COLLATION_1' COLLATE 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 IS08BIT. For example, here are two equivalent <character string literal>s:
N'Hello there'
_IS08BIT'Hello there'
If you want to restrict your code to Core SQL, don't use <national character string literal>s.