Character data: CHAR(n) and NCHAR(n)

The CHAR(n) data type contains a sequence of n bytes. These characters can be a mixture of English-language and non-English-language characters and can be either single byte or multibyte (Asian). The length n ranges from 1 to 32,767.

Whenever the database server retrieves or stores a CHAR(n) value, it transfers exactly n bytes. If an inserted value is shorter than n, the database server extends the value with single-byte ASCII space characters to make up n bytes. If an inserted value exceeds n bytes, the database server truncates the extra characters without returning an error message. Thus the semantic integrity of data for a CHAR(n) column or variable is not enforced when the value that is inserted or updated exceeds n bytes.

Data in CHAR columns is sorted in code-set order. For example, in the ASCII code set, the character a has a code-set value of 97, b has 98, and so forth. The database server sorts CHAR(n) data in this order.

The NCHAR(n) data type also contains a sequence of n bytes. These characters can be a mixture of English-language and non-English-language characters and can be either single byte or multibyte (Asian). The length of n has the same limits as the CHAR(n) data type. Whenever an NCHAR(n) value is retrieved or stored, exactly n bytes are transferred. The number of characters transferred can be fewer than the number of bytes if the data contains multibyte characters. If an inserted value is shorter than n, the database server extends the value with space characters to make up n bytes.

The database server sorts data in NCHAR(n) columns according to the order that the locale specifies. For more information about how to use locales, refer to the HCL OneDB™ GLS User's Guide.

Tip: The only difference between CHAR(n) and NCHAR(n) data is how you sort and compare the data. You can store non-English-language characters in a CHAR(n) column. However, because the database server uses code-set order to perform any sorting or comparison on CHAR(n) columns, you might not obtain the results in the order that you expect.

A CHAR(n) or NCHAR(n) value can include tabs and spaces but normally contains no other nonprinting characters. When you insert rows with INSERT or UPDATE, or when you load rows with a utility program, no means exist for entering nonprintable characters. However, when a program that uses embedded SQL creates rows, the program can insert any character except the null (binary zero) character. It is not a good idea to store nonprintable characters in a character column because standard programs and utilities do not expect them.

The advantage of the CHAR(n) or NCHAR(n) data type is its availability on all database servers. The only disadvantage of CHAR(n) or NCHAR(n) is its fixed length. When the length of data values varies widely from row to row, space is wasted.