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:

 ::=
[ _ ]'string' [ COLLATE  ]

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 '

[Obscure Rule] The optional Character set specification -- an underline character immediately preceding a <Character set name> (no space is allowed between them) -- names the Character set that the <literal> belongs to. 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 underline 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 Character 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". 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> up 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 other 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:

 ::=
N'string' [ COLLATE  ]

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 ISO8BIT. For example, here are two equivalent <character string literal>s:

N'Hello there'
_ISO8BIT'Hello there'

If you want to restrict your code to Core SQL, don't use <national character string literal>s.

Comments

Comments loading...