Oracle datatype: Should I use VARCHAR2 or CHAR

Question

Should I use VARCHAR2 or CHAR as a datatype in Oracle?

It's been suggested to me to use CHAR for these new tables I need but I'm concerned since these new tables which will be used to populat existing tables that use a VARCHAR2 datatype. I'm concerned about extra spaces being placed in the VARCHAR2 fields and with comparison issues. I know there are ways to compare them using by trimming or converting them but I'm afraid it will make my code messy and buggy.

What are your opinions?

Answer 1

Read:

• CHAR versus VARCHAR2 Semantics (documentation)
• Should I use char or varchar2 (AskTom)

Quote from AskTom article:

The fact that a CHAR/NCHAR is really nothing more than a VARCHAR2/NVARCHAR2 in disguise makes me of the opinion that there are really only two character string types to ever consider, namely VARCHAR2 and NVARCHAR2. I have never found a use for the CHAR type in any application. Since a CHAR type always blank pads the resulting string out to a fixed width, we discover rapidly that it consumes maximum storage both in the table segment and any index segments. That would be bad enough, but there is another important reason to avoid CHAR/NCHAR types: they create confusion in applications that need to retrieve this information (many cannot "find" their data after storing it). The reason for this relates to the rules of character string comparison and the strictness with which they are performed.

Answer 2

I'm concerned about extra spaces being placed in the VARCHAR2 fields and with comparison issues. I know there are ways to compare them using by trimming or converting them but I'm afraid it will make my code messy and buggy.

It's actually quite the opposite. Using CHAR will force your strings to be a fixed length by padding them with spaces if they're too short. So when comparing CHARs to regular strings in whatever app is using the data, that app would need to add a trim every time. In other words, VARCHAR2 is the choice that naturally leads to cleaner code.

In general you should always use VARCHAR2, unless you have a very specific reason why you want a CHAR column.

If you're worried about strings that have extra spaces in the front or end, then there's a few options that come to mind:

• Make sure whatever process is doing the inserts does a trim on them before inserting.
• Add a check constraint on the column that ensures that string = trim(string).
• Add a before insert row-level trigger that does a trim on the strings as they get inserted.
• Make sure that you do a trim on the strings whenever you query the table

Answer 3

CHAR has interesting comparison semantics. If you only use VARCHAR2, then you do not need to learn the CHAR semantics. Honestly, I think if I had a field with a known fixed lenth, I would still define it as a VARCHAR2 and use a check constraint to enforce it's fixed lengthiness, instead of learning the CHAR comparison semantics.

Some will argue that CHARs are more efficient for fixed length data because the length does not need to be stored, but that is untrue on Oracle.