Oracle NVARCHAR2

Summary: In this tutorial, you’ll learn about Oracle NVARCHAR2 data type and understand differences between NVARCHAR2 and VARCHAR2.

Introduction to Oracle NVARCHAR2 data type #

The NVARCHAR2 is a Unicode data type that can store Unicode character strings.

Here’s the syntax for NVARCHAR2:

NVARCHAR(n)Code language: SQL (Structured Query Language) (sql)

In this syntax, n is the number of characters. Each character uses 2 or 4 bytes depending on the character.

For example, English characters require 2 bytes whereas emjoi or Asian characters requires up to 4 bytes.

To find the national character of your database, you use the following query:

SELECT * FROM nls_database_parameters WHERE PARAMETER = 'NLS_NCHAR_CHARACTERSET';Code language: SQL (Structured Query Language) (sql)

In our Oracle database server, the NVARCHAR2 data type uses AL16UTF16 character set which encodes Unicode data in the UTF-16 encoding. The AL16UTF16 use 2 bytes to store a character.

Oracle NVARCHAR2 examples #

First, create a table with an NVARCHAR2 column that can store up to 50 characters:

CREATE TABLE nvarchar2_demo ( description NVARCHAR2(50) );Code language: SQL (Structured Query Language) (sql)

Since the current national character set is UTF-16, the description column requires up to 200 bytes depending on the characters used.

Second, insert a row into the nvarchar2_demo table:

INSERT INTO nvarchar2_demo VALUES(N'ABCDE');Code language: SQL (Structured Query Language) (sql)

In this example, we use N'' prefix to support literal format.

Third, find the detailed information on the value stored in the nvarchar2_demo table using the DUMP() function:

SELECT description, DUMP(description, 1016) FROM nvarchar2_demo;Code language: SQL (Structured Query Language) (sql)

Output:

The result shows that the datatype code is 1 and the length is 10 bytes (5 characters, 2 bytes each).

Fourth, insert an emoji (😊) into the table:

INSERT INTO nvarchar2_demo VALUES(N'😊');Code language: SQL (Structured Query Language) (sql)

Finally, retrieve data from the table:

SELECT description FROM nvarchar2_demo;Code language: SQL (Structured Query Language) (sql)

Output:

DESCRIPTION ----------- ABCDE 😊Code language: SQL (Structured Query Language) (sql)

VARCHAR2 vs. NVARCHAR2 #

The following table compares the VARCHAR2 and NVARCHAR2 data types:

Feature	VARCHAR2	NVARCHAR2
Purpose	Stores variable-length character data	Stores variable-length Unicode data
Character Set	Database character set (e.g., AL32UTF8)	National character set (e.g., UTF-16)
Encoding	Depends on DB character set	Always uses national character set
Storage Unit	Bytes (or characters if specified)	Characters only
Maximum Length	Up to 4000 bytes (or characters with CHAR mode)	Up to 4000 characters
Supports Multilingual?	Partially (depends on DB charset)	Fully Unicode-compliant (multilingual)
Uses More Space?	Typically less, esp. for ASCII	Can use more (up to 4 bytes per character)
Best For	Standard text, ASCII/Latin scripts	Multilingual apps needing full Unicode
Example	VARCHAR2(100)	NVARCHAR2(100)
Literal Prefix	'text'	N'text'
Index/Constraint Limits	Counts by bytes	Counts by characters

Here are the general guidelines:

• Use VARCHAR2 if you’re working only with English or Western European text since it is often more space-efficient .
• Use NVARCHAR2 for applications that handle multiple languages, especially Asian scripts or emoji.

Summary #

• Use NVARCHAR2 data type to store unicode characters.