VARCHAR2 Data Type

Description

The VARCHAR2 data type specifies a variable-length character string in the database character set. (Oracle SQL Language Reference VARCHAR2)

As denoted in the Oracle documentation, size in VARCHAR2 data type is a length constraint and should not be confused with capacity. Total characters that can be stored in a VARCHAR2 may vary according to the database character set and configuration, but commonly the maximum size allowed is 4000.

VARCHAR2 is translated to Snowflake VARCHAR which can store a bigger number of bytes/characters by default. Either way, the memory used is variable using the size of the value stored in the column as same as in Oracle.

VARCHAR2 (size [ BYTE | CHAR ])

Sample Source Patterns

Varchar2 data types in Create Table

Oracle

Query

CREATE TABLE varchar2_data_types
(
	varchar2_column1 VARCHAR2(5),
	varchar2_column2 VARCHAR2(5 BYTE),
	varchar2_column3 VARCHAR2(5 CHAR)
);

INSERT INTO varchar2_data_types VALUES ('H', 'Hello', 'Hell');

Snowflake

Query

CREATE OR REPLACE TABLE PUBLIC.varchar2_data_types (
varchar2_column1 VARCHAR(5),
varchar2_column2 VARCHAR(5),
varchar2_column3 VARCHAR(5));

INSERT INTO PUBLIC.varchar2_data_types VALUES ('H', 'Hello', 'Hell');

Retrieving data from varchar columns

Oracle

Query

SELECT * FROM varchar2_data_types;

Result

VARCHAR2_COLUMN1|VARCHAR2_COLUMN2|VARCHAR2_COLUMN3|
----------------+----------------+----------------+
H               |Hello           |Hell            |

Snowflake

Query

SELECT * FROM PUBLIC.varchar2_data_types;

Result

VARCHAR2_COLUMN1|VARCHAR2_COLUMN2|VARCHAR2_COLUMN3|
----------------+----------------+----------------+
H               |Hello           |Hell            |

Reviewing the variable size in the columns

Oracle

Query

SELECT
LENGTHB(varchar2_column1),
LENGTHB(varchar2_column2),
LENGTHB(varchar2_column3) 
FROM VARCHAR2_DATA_TYPES;

Result

LENGTHB(VARCHAR2_COLUMN1)|LENGTHB(VARCHAR2_COLUMN2)|LENGTHB(VARCHAR2_COLUMN3)|
-------------------------+-------------------------+-------------------------+
                        1|                        5|                        4|

Snowflake

Query

SELECT
OCTET_LENGTH(varchar2_column1) /*** MSC-WARNING - MSCEWI3091 - LENGTHB TRANSFORMED TO OCTET_LENGTH RESULTS MAY VARY DUE TO MEMORY MANAGEMENT OF DBMS ***/,
OCTET_LENGTH(varchar2_column2) /*** MSC-WARNING - MSCEWI3091 - LENGTHB TRANSFORMED TO OCTET_LENGTH RESULTS MAY VARY DUE TO MEMORY MANAGEMENT OF DBMS ***/,
OCTET_LENGTH(varchar2_column3) /*** MSC-WARNING - MSCEWI3091 - LENGTHB TRANSFORMED TO OCTET_LENGTH RESULTS MAY VARY DUE TO MEMORY MANAGEMENT OF DBMS ***/
FROM PUBLIC.VARCHAR2_DATA_TYPES;

Result

OCTET_LENGTH(VARCHAR2_COLUMN1)|OCTET_LENGTH(VARCHAR2_COLUMN2)|OCTET_LENGTH(VARCHAR2_COLUMN3)|
------------------------------+------------------------------+------------------------------+
                             1|                             5|                             4|

Known Issues

No issues were found.

Related EWIs

1. MSCEWI3091: LENGTHB transformed to OCTET_LENGTH.