Was this helpful?
Character Data Types
Character data types are strings of ASCII characters. Upper and lower case alphabetic characters are accepted literally. There are two fixed-length character data types, char and c, and two variable-length character data types: varchar and text.
The maximum row length in a table is 1928 bytes. Therefore, the maximum length of a character column is 1928 minus any additional space requirements. Additional space requirements for character columns are as follows:
varchar columns require two additional bytes to store a length specifier
nullable char and varchar columns require one additional byte to store a null indicator
Char Data Type
Char strings can contain any printing or non-printing character, and the null character ("\0"). In uncompressed tables, char strings are stored blank-padded to the declared length. (If the column is nullable, char columns require an additional byte of storage.) For example, if you enter "ABC" into a char(5) column, five bytes are stored, as follows:
"ABC  "
In compressed tables, trailing blanks are removed from char columns. In general, if your application must preserve trailing blanks, use varchar.
Leading and embedded blanks are significant when comparing char strings (unlike c strings). For example, the following char strings are different:
"A B C"
"ABC"
When retrieving char strings using the question mark (?) wildcard character, you must include any trailing blanks you want to match. For example, to retrieve the following char string:
"ABC "
the wildcard specification must also contain trailing blanks:
"???  "
Length is not significant when comparing char strings. For example, the following char strings are equal, even though the second string contains trailing blanks:
"ABC" = "ABC "
Character is a synonym for char.
C Data Type
The c data type accepts only printing characters. Non-printing characters, such as control characters, are converted into blanks.
The DBMS ignores blanks when comparing c strings. For example, the c string:
"the house is around the corner"
is treated identically to:
"thehouseisaroundthecorner"
The c type is supported for backward compatibility, but char is the recommended fixed-length character data type.
Varchar Data Type
Varchar strings are variable-length strings, stored as a 2-byte (I2) length specifier followed by data. In uncompressed tables, varchar columns occupy their declared length. (If the column is nullable, varchar columns require an additional byte of storage.) For example, if you enter "ABC" into a varchar(5) column, the stored result is:
"03ABCxx"
where "03" is a 2-byte length specifier, "ABC" is three bytes of data, and "xx" represents two bytes containing unknown (and irrelevant) data.
In compressed tables, varchar columns are stripped of trailing data. For example, if you enter "ABC" into a varchar(5) column in a compressed table, the stored result is:
"03ABC"
The varchar data type can contain any character, including non-printing characters and the ASCII null character ("\0").
Blanks are significant in the varchar data type. For example, the following two varchar strings are not considered equal:
"the store is closed"
and
"thestoreisclosed"
If the strings being compared are unequal in length, the shorter string is padded with trailing blanks until it equals the length of the longer string.
For example, consider the following two strings:
"abcd\001"
where "\001" represents one ASCII character (Control-A) and
"abcd"
If they are compared as varchar data types, then
"abcd" > "abcd\001"
because the blank character added to "abcd" to make the strings the same length has a higher value than Control-A ("\040" is greater than "\001").
Text Data Type
All ASCII characters except the null character ("\0") are allowed within text strings; null characters are converted to blanks.
Blanks are not ignored when you compare text strings. Unlike varchar, if the strings are unequal in length, blanks are not added to the shorter string. For example, assume that you are comparing the text strings
"abcd"
and
"abcd "
The string "abcd " is greater than the string "abcd" because it is longer.
Text is supported for backward compatibility, but varchar is the preferred varying length character type.
Last modified date: 04/03/2024