NVARCHAR (2024)

NVARCHAR is a locale-sensitive character data type that allows storing character data in variable-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 NVARCHAR variables both as program variables in your 4gl modules and for working with databases.

NVARCHAR variables defined in 4gl modules

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

DEFINE var NVARCHAR(18181)

With NVARCHAR(max), max specifies the maximum number of bytes that can be stored in the variable. The minimum size of the NVARCHAR value is 1 byte. The total length of an NVARCHAR variable cannot exceed 65,534 bytes.

A variable declared as NVARCHAR without parameters has a maximum size of 1 byte.

NVARCHAR columns in databases

With NVARCHAR(max, reserve),

• max specifies the maximum number of bytes that can be stored in the column, and
• reserve specifies the minimum number of bytes that can be stored in the column.

The minimum size of the NVARCHAR value is 1 byte. The total length of an NVARCHAR column cannot exceed 255 bytes. You can store character strings that are shorter - but not longer - than the specified value.

You must always specify the max parameter.

You may specify the reserve parameter if you initially use this column for values having few or no characters in this column but expect that later this data will be updated with longer values. reserve can range from 0 to 255 bytes but must be less than max. If you do not specify the minimum space value, the default value of reserve is 0.

A column declared as NVARCHAR without parameters has a maximum size of one byte, and a reserved size of zero.

Although NVARCHAR saves the space in a table, it has no effect on the size of an index. In indexes based on NVARCHAR columns, each index key has a length equal to max (i.e. the maximum size of the column).

The database server does not strip NVARCHAR objects of any user-entered trailing white spaces, nor does it pad them to the full length of the column. However, if you specify the reserve parameter (a minimum reserved space) but enter data values shorter than this, some reserved space goes unused.

Multibyte characters with NVARCHAR

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

You should be careful when using NVARCHAR columns to store multibyte characters: Themaxparameter of the NVARCHAR 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, sizedoes not indicate how many characters this column can hold - it indicates how many bytes are available to store multibyte characters. This means that you have to calculate thesizeof the NVARCHAR column with forethought so that it can hold enough multibyte characters for your purposes.

Digits with NVARCHAR

The number of digits in a numeric NVARCHAR value is the number of characters that you must store that value.

The database server does not pad numeric values in NVARCHAR columns with trailing blanks up to the maximum length of the column.

Non-printable characters with NVARCHAR

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

The database server interprets the NULL character (= ASCII 0) as a C null terminator. With NVARCHAR, the null terminator acts as a string terminating character.

Handling NVARCHAR data

Client server manipulates NVARCHAR data by using theCLIENT_LOCALEsettings of the client system.

Client server performs the code-set conversion of NVARCHAR data automatically ifCLIENT_LOCALEdiffers fromDB_LOCALE.

Collating NVARCHAR data

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

In contrast, data in VARCHAR 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 VARCHAR 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 NVARCHAR 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 NVARCHAR data in code-set order in the default locale (i.e. in the same way as sorts VARCHAR data).

Database server operations on NVARCHAR 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 NVARCHAR columns.