Character Data Types
Character data types are strings of characters. Upper and lower case alphabetic characters are accepted literally. There are two fixed-length character data types: char and c, and three variable-length character data types: varchar, long varchar, and text.
The maximum row length is dependent on the default_page_size setting (a DBMS Server configuration parameter), and can be set to a maximum of 32,767 bytes. For further information on page and row size configuration, see the Database Administrator Guide.
The maximum length of a character column is limited by the maximum row width configured, but cannot exceed 32,000 bytes for a non-UTF8 installation and 16,000 bytes for a UTF8 installation. Long varchar columns are an exception: the maximum length of these columns is 2 GB.
C Data Types
Fixed-length c data types accept only printing characters. Non-printing characters, such as control characters, are converted into blanks.
Blanks are ignored when c strings are compared, including when compared against other character data types. For example, this c string:
'the house is around the corner'
is considered equal to:
'thehouseisaroundthecorner'
Note: Char is the preferred fixed length character type. C is supported for backward compatibility.
Char Data Type
Fixed-length char strings can contain any printing or non-printing character, and the null character ('\0'). In uncompressed tables, char strings are padded with blanks to the declared length. (If the column is nullable, char columns require an additional byte of storage.) For example, if ABC is entered into a char(5) column, five bytes are stored, as follows:
'ABC '
Leading and embedded blanks are significant when comparing char strings. For example, the following char strings are considered different:
'A B C'
'ABC'
When selecting char strings using the underscore (_) wildcard character of the LIKE predicate, include any trailing blanks to be matched. For example, to select the following char string:
'ABC '
the wildcard specification must also contain trailing blanks:
'_____'
Length is not significant when comparing char strings; the shorter string is (logically) padded to the length of the longer. For example, the following char strings are considered equal:
'ABC'
'ABC '
Note: A synonym for char is character.
Text Data Types
All ASCII characters except the null character (\0) are allowed within text strings. Null characters are converted to blanks.
Blanks are only ignored when text strings are compared with C data. Unlike varchar, if the strings are unequal in length, blanks are not added to the shorter string. For example, assume that the following text strings are being compared:
'abcd'
and
'abcd '
The string 'abcd ' is considered greater than the string 'abcd' because it is longer.
Note: Varchar is the preferred varying length character type. Text is supported for backward compatibility.
Varchar Data Type
Varchar strings are variable-length strings, stored as a 2-byte (smallint) length specifier followed by data. In uncompressed tables, varchar columns occupy their declared length. For example, if ABC is entered into a varchar(5) column, the stored result is:
'03ABCxx'
where:
• 03 is a 2-byte length specifier.
• ABC is three bytes of data.
• xx represents two bytes containing unknown (and irrelevant) data.
If the column is nullable, varchar columns require an additional byte of storage.
In compressed tables, varchar columns are stripped of trailing data. For example, if “ABC” is entered into a varchar(5) column in a compressed table, the stored result is:
'03ABC'
The varchar data type can contain any character, including non-printing characters and the ASCII null character ('\0').
Except when comparing with C data, blanks are significant in the varchar data type. For example, the following two varchar strings are not considered equal:
'the store is closed'
and
'thestoreisclosed'
If the strings being compared are unequal in length, the shorter string is padded with trailing blanks until it equals the length of the longer string.
For example, consider the following two strings:
'abcd\001'
where:
'\001' represents one ASCII character (ControlA)
and
'abcd'
If they are compared as varchar data types, then
'abcd' > 'abcd\001'
because the blank character added to 'abcd' to make the strings the same length has a higher value than ControlA ('\040' is greater than '\001').
Long Varchar Data Types
The long varchar data type has the same characteristics as the varchar data type, but can accommodate strings up to 2 GB in length.
If a length is declared for this type of column, it represents the size of the inline space to store the initial n bytes of data for the large object. For more information, see Storing BLOBs Inline the Database Administrator Guide.
In embedded SQL,
data handlers can be created, which are routines to read and write the data for columns that contain large objects. For more information on data handlers, see
Data Handlers for Large Objects on page 277 and the
Embedded SQL Companion Guide.
Restrictions on Long Varchar Columns
The following restrictions apply to long varchar columns:
• They cannot be part of a table key.
• They cannot be part of a secondary index.
• They cannot be used in the ORDER BY or GROUP BY clause in a SELECT statement.
• They can be included in a select list with the DISTINCT qualifier, but duplicate values will not be eliminated.
• They cannot have query optimization statistics. For details about query optimization statistics, see the optimizedb command in the Command Reference Guide.
• They are not considered as potential numeric data with mixed-type comparisons.
• The following string functions do not work with long varchar columns:
– LOCATE
– PAD
– SHIFT
– SQUEEZE
– TRIM
– NOTRIM
– CHAREXTRACT
– BYTEEXTRACT
• They cannot be directly compared to other string data types. To compare a long varchar column to another string data type, apply a coercion function.
• A declared length for this type of column represents the size in bytes of the inline space, not a maximum size.
• A string literal of more than 2000 characters cannot be assigned to a long varchar column. Details about assigning long strings to these columns are found in the description of data handlers in the Embedded SQL Companion Guide or the OpenAPI User Guide.