18. Working with Data Types and Data Display Formats : Data Types : Character Data Types : How Character Strings Are Compared
 
Share this page                  
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