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. Characters use UTF-8 encoding.

Name Description Syntax

STRING

A NuoDB extension. NuoDB handles string types of potentially unlimited in length.

STRING
The maximum size of the combined length of the values of columns in a row must be less than 1.9 GB. If the size exceeds 1.9 GB, the update is rejected, reporting "width of record being inserted is too large: size of <size_of_the_row> exceeds system limit of 1900000000".

Character Types

Name Description Alias[es] Syntax

CHARACTER

variable-length character string

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

CHARACTER (n)

CHARACTER LARGE OBJECT

unlimited character length

CHAR LARGE OBJECT,
CLOB,
NCHAR LARGE OBJECT,
NCLOB,
TEXT

CHARACTER LARGE OBJECT

Strings are stored within the data record for a row. Due to the way in which NuoDB implements transaction isolation, each update to a record will create a new complete record (cloning the existing non updated values). For a very large String which is updated often, the record data can become quite large, causing performance issues. CLOBs, on the other hand, are stored as a dedicated data record with a reference to that record stored in the table row record. 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.

String Input

String literals are specified as single quoted strings. Example:

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

 abc

String literals may be represented as N'string'. For example:

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

 abc
 def
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.

String literals with ' inside the string can be specified by preceding with another '. For example:

INSERT INTO tbl VALUES ('abc''12');
SELECT * FROM tbl;
  X
 ---

 abc
 def
 abc'12