Character variables strip the trailing spaces from strings when the variable is populated in a varchar column of a cursor.
Use n to specify the number of bytes of storage for char and varchar datatypes. For unichar, use n to specify the number of unicode characters (the amount of storage allocated is 2 bytes per character). For nchar and nvarchar, n is the number of characters (the amount of storage allocated is n times the number of bytes per characer for the server’s current default character set).
If you do not use n to specify the length:
The default length is 1 byte for columns created with create table, alter table, and variables created with declare.
The default length is 30 bytes for values created with the convert function.
Entries shorter than the assigned length are blank-padded; entries longer than the assigned length are truncated without warning, unless the string_rtruncation option to the set command is set to on. Fixed-length columns that allow nulls are internally converted to variable-length columns.
Use n to specify the maximum length in characters for the variable-length datatypes, varchar(n), univarchar(n), and nvarchar(n) . Data in variable-length columns is stripped of trailing blanks; storage size is the actual length of the data entered. Data in variable-length variables and parameters retains all trailing blanks, but is not padded to the defined length. Character literals are treated as variable-length datatypes.
Fixed-length columns tend to take more storage space than variable-length columns, but are accessed somewhat faster. Table 1-16 summarizes the storage requirements of the different character datatypes:
Datatype |
Stores |
Bytes of storage |
|---|---|---|
char(n) |
Character |
n |
unichar(n) |
Unicode character |
n*@@unicharsize (@@unicharsize equals 2) |
nchar(n) |
National character |
n * @@ncharsize |
varchar(n) |
Character varying |
Actual number of characters entered |
univarchar(n) |
Unicode character varying |
Actual number of characters * @@unicharsize |
nvarchar(n) |
National character varying |
Actual number of characters * @@ncharsize |
Use the char_length string function and datalength system function to determine column length:
char_length returns the number of characters in the column, stripping trailing blanks for variable-length datatypes.
datalength returns the number of bytes, stripping trailing blanks for data stored in variable-length columns.
When a char value is declared to allow NULLS, Adaptive Server stores it internally as a varchar.
If the min or max aggregate functions are used on a char column, the result returned is varchar, and is therefore stripped of all trailing spaces.
