T062, “Character length units”

1. Non-standard alternatives available
2. Only octets
3. Not for national types

For character strings, SQL allows to declare whether the length means characters or octets, i.e. bytes.

CREATE TABLE … (
  c1 VARCHAR(10),
  c2 VARCHAR(10 CHARACTERS),
  c3 VARCHAR(10 OCTETS)
)

In absence of an explicit length unit, characters is default.✓✗0 The following chart shows that not all systems apply that default correctly. Note that the behavior might also depend on installation defaults, like the character encoding, so that your system might behave differently.

1. Characters for national types, depends on nls_string_units for the other types
2. 16 bit units for national types, depends on nls_length_semantics for the other types
3. Length limitation not enforced
4. When using UTF8
5. Observed with the default character set

Non-Standard Alternatives

The standard defines the keywords characters and octets (both plural) for the length units. Yet some systems support different words only.

1. Not for national types

Related

Mandatory Features

• E021-01, “CHARACTER data type (including all its spellings)”

• E021-02, “CHARACTER VARYING data type (including all its spellings)”

Optional Features

• F421, “National character”

• T046, “CLOB data type”

  • T043, “Multiplier T”

  • T044, “Multiplier P”

• T081, “Optional string types maximum length”

Implementation-defined Elements

• ID068, “The implicit large object length when large object length is omitted.”

• ID069, “The implicit character maximum length when character maximum length is omitted.”

• IL042, “The maximum lengths for character string types and binary string types.”

Normative References

The <character length unit> was introduced with ISO/IEC 9075-2:2003 as part of the optional feature T061, “UCS support”. With the 2023 edition of the standard, it has got its own Feature ID: T062, “Character length units”.