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.
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:
Data in fixed-length columns is blank-padded to the column length. For char and unichar datatypes, storage size is n bytes, (unichar = n*@@unicharsize); for nchar, n times the average national character length (@@ncharsize). When you create a char, unichar, or nchar column that allows nulls, Adaptive Server converts it to a varchar, univarchar, or nvarchar column and uses the storage rules for those datatypes. This is not true of char and nchar variables and parameters.
Data in variable-length columns is stripped of trailing blanks; storage size is the actual length of the data. For varchar or univarchar columns, this is the number of characters; for nvarchar columns, it is the number of characters times the average character length. Variable-length character data may require less space than fixed-length data, but is accessed somewhat more slowly.
