SQL String and Character Types

String Type

Character types are data types designed for storing text with either a fixed or variable number of characters.

Name Description Syntax
STRING A NuoDB extension. NuoDB handles string types as potentially unlimited in length. NuoDB recommends that you use String types instead of Character types. Characters are all stored in UTF-8. An empty string or a string containing only spaces and assigned to a numeric value becomes zero. STRING

Character Types

Name Description Alias[es] Syntax
CHARACTER variable-length character string CHAR
CHARACTER VARYING (n)
VARCHAR (n)
CHARACTER (n)
CHARACTER LARGE OBJECT unlimited character length CHAR LARGE OBJECT
CLOB
NCHAR LARGE OBJECT
NCLOB
TEXT
CHARACTER LARGE OBJECT
NATIONAL CHARACTER specifies predefined national character set, synonymous with char.

NATIONAL CHAR(n)
NCHAR(n)
NATIONAL CHARACTER VARYING(n)
NATIONAL CHAR VARYING(n)

NATIONAL CHARACTER (n)
TEXT unlimited character length CLOB TEXT

Note: For character varying data types, if n equals 0, the default length is 1.

Strings are stored within the entire data record for a row. Character Large Objects (CLOBs) are stored in separate atomThe internal object structure representing all data in a NuoDB database. Atoms are self-coordinating objects that represent specific types of information (such as data, indexes or schemas).s. CLOBs are not versioned. So there is only one version at any one time for a CLOB field in a record. Strings have multiple versions tracking any changes to that particular field in a record. Strings are encoded in the table row record and is cloned every time it is updated. If you have a very large string which is updated often, the row record can become very large and cause network issues. CLOBs are stored as a dedicated data record with a pointer to the record in the table row record and updates to CLOBs do not result in large row records. As a result, the choice between using CLOBs and strings depends on the application access and update pattern.

Storing Values for String and Character

CLOB values are stored separately from the rest of the contents of their record, and only a reference to the BLOB value (for more information, see SQL Binary Types) is kept in the record. Only SELECT statements which request the contents of the CLOB field will load it, and changes to other fields in the record will not cause the CLOB value to be rewritten. However, because it's stored separately it does take slightly more time to retrieve the value when it is required.

String Input

String literals are specified as single quoted strings. Example:

SQL> CREATE TABLE tbl(x STRING);
SQL> INSERT INTO tbl VALUES ('abc');
SQL> SELECT * FROM tbl; X --- abc

String literals may be represented as N'string' as in this example:

SQL> INSERT INTO tbl VALUES (N'def');
SQL> SELECT * FROM tbl; X --- abc def

Note: Since all NuoDB characters are Unicode characters, the N prefix is redundant. However, NuoDB still supports the N'string' syntax (when using ASCII characters) for compatibility with other databases.