1. Varchar

varchar [ ( n | max ) ] Variable-size string data. Use n to define the string size in bytes and can be a value from 1 through 8,000 or use max to indicate a column constraint size up to a maximum storage of 2^31-1 bytes (2 GB).

Because the this type can define a maximum byte of character want to stored. If you store a string and each word byte is different 1. You should consider to user NVarchar or not.

For VARCHAR columns, trailing spaces in excess of the column length are truncated prior to insertion and a warning is generated, regardless of the SQL mode in use. Please note that is not same with CHAR type, they always trailing spaces without SQL mode.

Just see the example about CHAR and VARCHAR below:

// Just remember that CHAR always remove trailing spaces. VARCHAR is not
mysql> CREATE TABLE vc (v VARCHAR(4), c CHAR(4));
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO vc VALUES ('ab  ', 'ab  ');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT CONCAT('(', v, ')'), CONCAT('(', c, ')') FROM vc;
+---------------------+---------------------+
| CONCAT('(', v, ')') | CONCAT('(', c, ')') |
+---------------------+---------------------+
| (ab  )              | (ab)                |
+---------------------+---------------------+
1 row in set (0.06 sec)

2. NVarchar

NVarchar is data type of database, is used to store variable-length, Unicode string data.

The main difference between Varchar and NVarchar is size of byte store. With me, when I work in Japanese Company (Persol Process Technology VN). In database, they used many column with data type NVarchar. Because the input in screen app is Japanese Character, if you store it with Varchar type. The problem will come.

The japanese character is two byte for each word. To see the length different, we see the example below:

// The LEN function will count length of character
// Otherwise, function DATALENGTH count length byte of character
SELECT LEN('如抜範浪偃壅國'), DATALENGTH('如抜範浪偃壅國'); -- 7, 14

That’s why when we work with Japanese character or Arab, we should use NVarchar

// When you want to get the correct character, you should use N.
SELECT c FROM dbo.t WHERE c = 'រៀន';
SELECT c FROM dbo.t WHERE c = N'រៀន'
----
??? -- not stored correctly

3. Varchar2

There is one benefit about VARCHAR2 you should know.

For example, if you define a VARCHAR2 column with a maximum size is 20. In a single-byte character set, you can store up to 20 characters. If you store 21 characters or more, Oracle returns an error.

In addition, if you store 10 characters in a VARCHAR2(20) column, Oracle uses only 10 bytes for storage, not 20 bytes. Therefore, using VARCHAR2 data type helps you save spaces used by the table. In some case, store a String with VARCHAR2 type is better than VARCHAR. Take a note.

Futhermore, we should know why we should use VACHAR2 instead for CHAR(n)

The difference is that CHAR(n) will ALWAYS be n bytes long. If the string length is <n, it will be blank padded upon insert to ensure a length of n. A VARCHAR2(n) on the other hand will be 1 to n bytes long. A shorter string stored as VARCHAR2 will NOT be blank padded.

// When use CHAR type, if the input string length is not match
// with n define when create data, they will auto fill blank space
// In some case, that's very dangerous
SELECT 
    '"'||CAST('abc' AS VARCHAR2(10))||'"', 
    '"'||CAST('abc' AS CHAR(10))||'"' 
FROM dual;


'"'||CAST('ABC'ASVARCHAR2(10))||'"' '"'||CAST('ABC'ASCHAR(10))||'"'
----------------------------------- -------------------------------
"abc"                               "abc       "                   
1 row selected.

4. Reference

• Oracle Varchar2.
• Oracle DataTypes.
• The CHAR and VARCHAR type.

You can read more article about database in Kieblog. Like Oracle to Postgres!.