Was this helpful?
Character Data Types
Four different data types define text:
c
char
text
varchar
You enter character data as alphanumeric strings. You must enclose the string in single quotes ('). To include an explicit single quote (or apostrophe) within a character string, dereference it by preceding it with another single quote. For example:
'Enter your supervisor''s initials'
You can include double quotes within a single‑quoted character string without dereferencing them:
'according to the "experts"'
In displaying a query result, SQL displays character data as alphanumeric strings without the surrounding single quotes. SQL differentiates between uppercase and lowercase data in character strings. For example, suppose you use this string in an SQL statement:
'Bee, Charles'
It is evaluated differently from any of the strings below:
'BEE, CHARLES'
'bee, charles'
'Bee, charles'
C Data Type
The c data type consists of a string of up to n printable ASCII characters. It converts non‑printable characters to blanks; n represents the lesser of the maximum configured row size and 32,000. Blanks are ignored when comparing strings of the c data type. For example, it treats the following two examples identically:
newtable and oldtable
newtableandoldtable
Char Data Type
The char data type consists of a string of up to n printing ASCII characters, including any non‑printable characters; n represents the lesser of the maximum configured row size and 32,000. Between c and char, the char data type is preferred, as it is compatible with ANSI SQL.
Text Data Type
The text data type consists of up to n characters in the ASCII extended set or blanks; n represents the lesser of the maximum configured row size and 32,000. All ASCII characters except the ASCII null character (null) or the hex \0 are allowed.
Varchar Data Type
The varchar data type consists of up to n characters in the ASCII extended set or blanks; n represents the lesser of the maximum configured row size and 32,000. All ASCII characters are permitted, including non‑printable control characters, such as the NULL character. A varchar column is defined as varchar(n) where n is the maximum number of characters stored in the column. Between text and varchar, the varchar data type is preferred as it is compatible with ANSI SQL.
How Character Strings Are Compared
When comparing two character strings, it is important to consider the range of ASCII characters permitted by a given data type. Including non‑printable characters in varchar or char comparison strings affects the outcome of the comparison.
Blanks are not ignored in comparisons by either text or varchar. However, the way blanks are handled by the two data types differs. In comparing strings of unequal length, varchar effectively adds blanks at the end of the shorter string to bring it to the same length as the longer string. The text data type does not add blanks; it considers a shorter string as less than a longer string if all characters up to the length of the shorter string are equal.
As an example of the way this affects comparisons, consider the following two strings:
abcd\001
abcd
Assume that \001 represents one ASCII character, ControlA. If these are compared as text, the first string is greater than the second. However, if they are compared as varchar, the first string is less than the second, because the blanks added by varchar to the shorter string have a higher ASCII value than 001.
The following table summarizes the various character data types, where n represents the lesser of the maximum configured row size and 32,000:
Aspect
c
char
text
varchar
Length
1‑n
1‑n
1‑n
1‑n
Nullable
Yes
Yes
Yes
Yes
Legal characters
Printable characters only
All ASCII characters
All ASCII characters except nul and \0
All ASCII characters
Storage
Fixed
length
Fixed
length
Variable length
Variable length
Blanks significant?
No
Yes
Yes
Yes
Comparison of short to long
Blanks ignored
Blank padding of short length to long
No padding; blanks included
Blank padding of short length to long
Last modified date: 04/03/2024