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?
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:
Read:
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.
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.
I would suggest that you stick to VARCHAR2.
You should use CHAR when the data is a known fixed length.
Choose VARCHAR2(size) over CHAR(size), since this is more space and time efficient:
Surprisingly or not, CHAR(size) allows assignment of strings with a length len shorter than size. In this case, ORACLE appends size-len spaces to the string for datatypes CHAR and VARCHAR and stores size characters.
The VARCHAR2 datatype comes without padding, only len characters are stored.
CREATE TABLE Demo(col1 CHAR(4), col2 VARCHAR2(4));
INSERT INTO Demo (col1, col2) VALUES ('c', 'v');
As a result,
col1='c ' (padded with 3 trailing spaces, since the size of col1 is 4 and the length of 'c' is only 1). col1='c' evaluates FALSE, only TRIM(col1)='c' evaluates TRUE,
whereas
col2='v' evaluates TRUE withoutTRIM(), making the comparison more efficient.
Furthermore, comparisons between two VARCHAR2 values fail fast if their lengths differ (independent of their size). In this case, no character-wise comparisons are required.
With CHAR and same size, the length check always fails due to padding. Thus, every character has to be compared until the first character mismatch or the end-of-string has been reached, whichever occurs first.
Since both CHAR(size) and VARCHAR2(size) don't prevent assignments of values shorter than size, define a length constraint if you need to assure that only values with a predefined length (which should equal size) can be assigned.
