NCHAR Data Type

Description

The NCHAR data type specifies a fixed-length character string in the national character set. (Oracle SQL Language Reference NCHAR)

NCHAR allows to store special characters with their Unicode to be preserved across any usage, these special characters may need more bits to be stored and that is why, by default, the NCHAR character set is AL16UTF16, contrary to the common character data set for CHAR which is usually AL32UTF8.

NCHAR is preserved as NCHAR in Snowflake, but, in the background, Snowflake uses VARCHAR. Transformation information related to CHAR is also valid for NCHAR.

NCHAR [ (size) ]

Sample Souce Patterns

Nchar data types in Create Table

Oracle

Query

IN -> Oracle_01.sql

CREATE TABLE nchar_data_types
(
	nchar_column1 NCHAR,
	nchar_column2 NCHAR(5)
);

INSERT INTO nchar_data_types VALUES ('ភ', 'ភាសាខ');

Snowflake

Query

OUT -> Oracle_01.sql

CREATE OR REPLACE TABLE nchar_data_types
(
	nchar_column1 NCHAR,
	nchar_column2 NCHAR(5)
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

INSERT INTO nchar_data_types
VALUES ('ភ', 'ភាសាខ');

In Oracle, trying to insert these values in a CHAR column with the same size, will trigger an error: value too large for column.

Retrieving information from Nchar columns

Oracle

Query

IN -> Oracle_02.sql

SELECT * FROM nchar_data_types;

Result

NCHAR_COLUMN1|NCHAR_COLUMN2|
-------------+-------------+
ភ            |ភាសាខ        |

Snowflake

Query

OUT -> Oracle_02.sql

SELECT * FROM
nchar_data_types;

Result

NCHAR_COLUMN1|NCHAR_COLUMN2|
-------------+-------------+
ភ            |ភាសាខ        |

Retrieving the size in bytes of each column

Oracle

Query

IN -> Oracle_03.sql

SELECT 
LENGTHB(nchar_column1),
LENGTHB(nchar_column2)
FROM nchar_data_types;

Result

LENGTHB(NCHAR_COLUMN1)|LENGTHB(NCHAR_COLUMN2)|
----------------------+----------------------+
                     2|                    10|

Snowflake

Query

OUT -> Oracle_03.sql

SELECT
OCTET_LENGTH(nchar_column1) /*** SSC-FDM-OR0015 - LENGTHB TRANSFORMED TO OCTET_LENGTH RESULTS MAY VARY DUE TO MEMORY MANAGEMENT OF DBMS ***/,
OCTET_LENGTH(nchar_column2) /*** SSC-FDM-OR0015 - LENGTHB TRANSFORMED TO OCTET_LENGTH RESULTS MAY VARY DUE TO MEMORY MANAGEMENT OF DBMS ***/
FROM
nchar_data_types;

Result

OCTET_LENGTH(NCHAR_COLUMN1)|OCTET_LENGTH(NCHAR_COLUMN2)|
---------------------------+---------------------------+
                          3|                         15|

Note that the number specified in the column declaration is the size in characters and not in bytes, That is why we see more space used to store those special characters.

In Snowflake, VARCHAR uses UTF-8, size can vary depending on the Unicode character that can be represented in 1, 2, 3, or 4 bytes. In this case, the Cambodian character is using 3 bytes to be stored.

Besides these slight differences, the integrity of the data is preserved.

Known Issues

1. Results obtained from some built-in functions may vary

As explained in the previous section, there may be cases using built-in functions over the columns that may retrieve different results. For example, get the length of a column.

Related EWIs

1. SSC-FDM-OR0015: LENGTHB transformed to OCTET_LENGTH.