Oracle NVARCHAR2 Data Type

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.

Was this tutorial helpful?