Skip to content
Related Articles

Related Articles

Improve Article
Save Article
Like Article

Difference between char, varchar and VARCHAR2 in Oracle

  • Last Updated : 09 Aug, 2021

1. CHAR :
The char data type is used to store the character values. It is a fixed-length data type i.e once initialized we cannot change the size at execution time. Hence, it is also called a Static datatype.
It is used to store normal characters and alphanumeric characters too. The char data type can store a character string of a maximum length of 2000 bytes of characters. Also, for every one character, one byte is stored in the memory. Since the size is fixed and cannot be changed, it will lead to memory wastage.
Forex : If you store 5 characters in char(10), then the 5 bytes will be stored by oracle and the remaining 5 bytes will be padded to the right side leading to memory wastage as shown in the example in the latter part of the article.
.
The syntax is as follows.

char_name CHAR(length BYTE)
char_name CHAR(length CHAR)

NOTE : If the size of the character is not specified explicitly then the oracle will assign 1 byte by default something like this.

// Assigns 1 byte
char_name CHAR 

2. VARCHAR :
The VarChar data type is used to store the character values. It is a variable-length data type i.e we can change the size of the character at the time of the execution. Hence, it is also called a Dynamic datatype.
It is used to store normal characters and alphanumeric characters too. The VarChar data type can store a character string of a maximum length of 4000 bytes. Also, for every one character, one byte is stored in the memory.

VARCHAR is an ANSI Standard that is used to distinguish between Null and Empty Strings. However, in Oracle VARCHAR and VARCHAR2 is totally the same.
It is recommended to not use VARCHAR as Oracle may change its usage in the near future.

The syntax is as follows :



char_name VARCHAR(length BYTE)
char_name VARCHAR(length CHAR)

3. VARCHAR2 :
VARCHAR2 is the same as VARCHAR in the oracle database. The main difference is that VARCHAR is ANSI Standard and VARCHAR2 is Oracle standard.
The VarChar2 data type is used to store the character values. It is a variable-length data type i.e we can change the size of the character variable at execution time. Hence, it is also called a Dynamic datatype.
It is used to store normal characters and alphanumeric characters too. The VARCHAR2 data type can store a character string of a maximum length of 4000 bytes of characters. Also, for every one character, one byte is stored in the memory.  Since it is a Dynamic datatype, memory will not be wasted.
Forex : If you store 5 characters in char(10), then only 5 bytes will be stored by Oracle instead of 10 as in the case of VARCHAR and CHAR.

The Syntax is similar to that of VARCHAR

char_name VARCHAR2(length BYTE)
char_name VARCHAR2(length CHAR)

// allocates length bytes of character
char_name VARCHAR2(length) 

Now, let’s understand the difference further through a query in Oracle DataBase.

Let’s create a table which contains three variable a,b,c of char,varchar and varchar2 respectively. 

// creating a table
create table t(a char(10) , b varchar(10) , c varchar2(10)) 

Inserting values into the table

// inserting the values in the table
insert into t(a,b,c) values('rahul','krishna','loki') 
// for displaying table values
select * from t; 

Retrieving detailed information of a and b

// dump() function is used to obtain detailed information of a and b 
select a,dump(a),b,dump(b),c,dump(c) from t 



As you can see in the above example :

  1. Rahul is stored in char datatype and is of length 5 characters. Since it is a Char variable Oracle allocates space for Rahul and the remaining 5 characters are padded to the right. This leads to memory wastage.
     
  2. Krishna is stored in VarChar datatype and is of length 7 characters. Since it is a VarChar variable Oracle allocates space for all 7 characters only unlike the case in VarChar.
     
  3. Loki is stored in VarChar2 datatype and is of length 4 characters. Since it is a VarChar2 variable it allocates space for all the 4 characters only even though we declared the size as 10 bytes, similar to Krishna as discussed in the 2nd point.

Difference between char, varchar and VARCHAR2 in Oracle :

Sno   

Char

VarChar/VarChar2

1Char stands for “Character”VarChar/VarChar2 stands for Variable Character
2It is used to store character string of fixed lengthIt is used to store character string of variable length
3It has a Maximum Size of 
2000 Bytes
It has a Maximum Size of 4000 Bytes  
4Char will pad the spaces to the right side to fill the length specified during the DeclarationVarChar will not pad the spaces to the right side to fill the length specified during Declaration. 
5It is not required to specify the size at the time of declaration. It will take 1 Byte as default It is  required to specify the size at the time of declaration
6It is Static Datatype(i.e Fixed Length)It is Dynamic Datatype(i.e Variable Length)
7It can lead to memory wastage It manages Memory efficiently
8It is 50% much faster than VarChar/VarChar2 It is relatively slower as compared to Char

Note : There is no difference between VarChar and VarChar2 in Oracle. However, it is advised not to use VarChar for storing data as it is reserved for future use for storing some other type of variable. Hence, always use VarChar2 in place of VarChar.

Hence it is advised to use Char datatype when the length of the character string is fixed and will not change in the future.
If the length of the character string is not fixed, then VarChar2 is preferred. 

 

My Personal Notes arrow_drop_up

Start Your Coding Journey Now!