Automatic padding of character columns

When considering whether to use CHAR or VARCHAR, the decision depends more on programming considerations than on the database. The difference is that comparisons of two fixed character fields will be automatically padded to equal lengths with spaces; comparisons with VARCHAR will not.

Why not just make everything VARCHAR?

Fixed vs. variable length program variables

One reason that the VARCHAR data type is often used is that some programmers by habit use variable length character variables, which will also defeat the programming language's automatic padding for fixed-length comparisons. For example, in Java, programmers might simply use String, which is variable length, for all character variables and avoid char[], which is the appropriate type for storing data being transferred to or from a CHAR column in a database.

When to use CHAR vs. VARCHAR

In database design, we're taught how to determine whether to make a column CHAR or VARCHAR. You ask yourself, "is the length of the value significant?" For example, if the column has up to 6 characters representing a gender, is a 6-character "MALE " different from a 4-character "MALE"? In this case the answer is obviously "no" and the column should therefore be CHAR so that regardless of whether you compare it to a 4- or 6-character constant or 6-character host variable, the comparison is automatically padded and works properly regardless of whether the column contains "MALE " or "FEMALE" and whether the constant or host variable contains "MALE", "MALE " or "FEMALE". If either side of the comparison is a variable length character type, then you will need to RPAD and/or RTRIM one or both sides of the comparison to make it work properly.