Character datatypes

Use the character datatypes to store strings consisting of letters, numbers, and symbols entered within single or double quotes. Use the like keyword to search these strings for particular characters, and the built-in string functions to manipulate their contents. Use the convert function to convert strings consisting of numbers to exact and approximate numeric datatypes, which can then be used for arithmetic.

The char(n) datatype stores fixed-length strings, and the varchar(n) datatype stores variable-length strings, in single-byte character sets such as English. Their international character counterparts, nchar(n) and nvarchar(n), store fixed- and variable-length strings in multibyte character sets such as Japanese.The unichar and univarchar datatypes store Unicode characters, which are a constant size. You can specify the maximum number of characters with n or use the default column length of one character. For strings larger than the page size, use the text datatype.

Table 6-3: Character datatypes

Datatype

Stores

char(n)

Fixed-length data, such as social security numbers or postal codes

varchar(n)

Data, such as names, that is likely to vary greatly in length

unichar

Fixed-length Unicode data, comparable to char

univarchar

Unicode data that is likely to vary greatly in length, comparable to varchar

nchar(n)

Fixed-length data in multibyte character sets

nvarchar(n)

Variable-length data in multibyte character sets

text

Up to 2,147,483,647 bytes of printable characters on linked lists of data pages

unitext

Up to 1,073,741,823 Unicode characters on linked lists of data pages

Adaptive Server truncates entries to the specified column length without warning or error, unless you set string_rtruncation on. See the Reference Manual: Commands. The empty string, “ ”or ‘ ’, is stored as a single space rather than as NULL. Thus, “abc” + “ ” + “def” is equivalent to “abc def”, not to “abcdef”.

Fixed- and variable-length columns behave somewhat differently: