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
与恶龙缠斗过久,自身亦成为恶龙；凝视深渊过久,深渊将回以凝视…
