NCHAR

NCHAR is a locale-sensitive character data type that allows storing character data in fixed-length fields as strings of single-byte or multibyte letters, numbers, and other characters supported by the code set of the necessary database locale.

 

You can use NCHAR variables both as program variables in your 4gl modules and for working with databases.

NCHAR variables defined in 4gl modules

In 4gl modules, you can declare these variables directly with a NCHAR keyword:

DEFINE var NCHAR(25252)

With NCHAR(size), size specifies the number of bytes in the NCHAR variable. The minimum size of the NCHAR value is 1 byte. It is also the default maximum size of an NCHAR variable that is applied if the size is not defined explicitly (i.e. NCHAR = NCHAR(1)).  The total length of an NCHAR variable cannot exceed 65,534 bytes.

NCHAR columns in databases

With NCHAR(size), size specifies the number of bytes in the NCHAR column. The minimum size of the NCHAR value is 1 byte. It is also the default maximum size of an NCHAR column that is applied if the size is not defined explicitly (i.e. NCHAR = NCHAR(1)).  The total length of an NCHAR column cannot exceed 32,767 bytes.

 

Because the length of NCHAR columns is fixed, when the database server retrieves or sends an NCHAR value, it transfers exactly size bytes of data. If the length of a character string is shorter than size, the database server extends the string with spaces to make up the size bytes. If the string is longer than size bytes, the database server truncates the string. This means that an NCHAR(150) column can contain 150 characters (or less) and cannot contain 151 character (or more).

Multibyte characters with NCHAR

You can store multibyte character data in an NCHAR column only if your database locale supports a code set that includes multibyte characters.

 

You should be careful when using NCHAR columns to store multibyte characters: The size parameter of the NCHAR data type refers to the number of bytes reserved for storing the data but not to the number of logical characters.

 

Because one multibyte character requires several bytes for storage, size does not indicate how many characters this column can hold - it indicates how many bytes are available to store multibyte characters (NCHAR(12) is able to store three 4-byte characters (12=3*4) but not 12 4-byte characters). This means that you have to calculate the size of the NCHAR column carefully so that it can hold enough multibyte characters for your purposes.

Digits with NCHAR

NCHAR values can store any printable symbols including digits. However, if you want to perform arithmetic operations on numbers, you should store these numbers in variables of numeric data types. Likewise CHAR, NCHAR is generally compatible with number data types, but you might not be able to use them in some calculations.

In cases when numeric data types (like INTEGER or SMALLINT) do not meet your needs (e.g., if you need a column to store postal codes with leading zeros), you can and should use CHAR or NCHAR.

Non-printable characters with NCHAR

NCHAR values can include tabs, spaces, and other white space and non-printable characters. Non-printable NCHAR values are entered, displayed, and treated in the same way as non-printable CHAR values.

Handling NCHAR data

Client server manipulates NCHAR data by using the CLIENT_LOCALE settings of the client system.

Client server performs the code-set conversion of NCHAR data automatically if CLIENT_LOCALE differs from DB_LOCALE.

Collating NCHAR data

Data in NCHAR columns are sorted in localized order if the locale defines a localized order.

In contrast, data in CHAR columns are sorted in the code-set order for most operations, even if the database locale defines a localized collation.

 

For example, the French locale specifies that the character ê is sorted after e and before f. In CHAR columns, you might not obtain the sorting results in the order you need (e.g., values with ê will be mixed with values with e). In NCHAR columns, the sorting order with be the one you need (e, ê, f).

 

As the default Lycia locale is en_US that has no localized order, the database server will sort NCHAR data in code-set order in the default locale (i.e. in the same way as sorts CHAR data).

Database server operations on NCHAR columns ignore the case of letters and ordering their data values without respect to or preference for case (i.e. the string "ABC" might precede or follow the strings "abc", "Abc", or "aBc" in a collated list, depending on the order in which the data values are retrieved). This means that NLSCASE INSENSITIVE used when creating a database has no effect on NCHAR columns.