Oracle NVARCHAR vs VARCHAR: Differences, Conversion, and Common Issues

When working with Oracle databases, one of the most frequently misunderstood topics is the difference between NVARCHAR2 and VARCHAR2.
Developers often mix these two data types without realizing the impact, which can later result in character corruption, unexpected truncation, or performance issues.


This post explains the differences between the two, how Oracle performs implicit conversions, and what developers should keep in mind when handling multilingual data.




🔹 1. NVARCHAR2 vs VARCHAR2

NVARCHAR2
- Stores Unicode characters (UTF-16).
- Recommended when dealing with multilingual data (e.g., Korean, Chinese, Japanese).
- Each character is stored using 2 bytes, regardless of whether the character is ASCII or not.


VARCHAR2
- Stores characters in the database’s default character set (often AL32UTF8 or KO16MSWIN949 in older systems).
- Each character size may vary depending on the encoding (1–3 bytes).


If the database character set does not support certain symbols or multilingual characters, data corruption may occur.


In short: NVARCHAR2 is Unicode-safe, while VARCHAR2 depends on the database’s character set.




🔹 2. Implicit Conversion Rules

Oracle automatically aligns data types when multiple character types appear in the same expression. The precedence order is important to remember:

1. CHAR
2. VARCHAR2
3. NVARCHAR2


This means:
If you mix NVARCHAR2 and VARCHAR2 in a DECODE or CASE statement, the result will be converted to VARCHAR2.
If you mix CHAR, VARCHAR2, and NVARCHAR2 together, the result will be converted to CHAR.


⚠️ This implicit conversion often causes unexpected truncation or corruption when multilingual data is involved.




🔹 3. Example of Implicit Conversion

Even though the first branch uses NVARCHAR2, the final result may be forced into CHAR.
If the target columns contain Korean or Chinese characters, data corruption is very likely.




🔹 4. Common Issues with NVARCHAR and VARCHAR

1. Character Corruption with REVERSE Function
Using REVERSE on VARCHAR2 columns that store multilingual data may produce broken characters.
This is because REVERSE processes data byte by byte, not by character.


2. Storage and Indexing
NVARCHAR2 consumes more space but ensures data integrity.
Indexes on NVARCHAR2 columns may be slightly larger.


3. Application Compatibility
If the client application does not fully support Unicode, even NVARCHAR2 may appear corrupted.




🔹 5. Best Practices

Use NVARCHAR2 for any column that needs to store multilingual text.
Do not rely on implicit conversion; explicitly cast values using TO_NCHAR or TO_CHAR with proper settings.
Always confirm your database character set and NLS settings before migration or system integration.


Avoid mixing CHAR, VARCHAR2, and NVARCHAR2 in a single SQL expression whenever possible.




✅ Conclusion

Understanding the difference between NVARCHAR2 and VARCHAR2 is essential for database developers working with Oracle.
While VARCHAR2 may seem sufficient in single-language environments, any project that involves multilingual support should prefer NVARCHAR2.


At the same time, developers must be cautious about Oracle’s implicit conversion rules, as they can lead to unexpected behavior and data corruption.
Choosing the right data type from the start can save a lot of trouble during migration, integration, or scaling.