In this tutorial you will learn about difference between varchar and varchar2.
Varchar stands for variable length character string. Both Varchar and Varchar2 are data types to store character strings for particular column (field) in databases. These are reserved by ORACLE. If we relay empty string and NULL being the same, then we should use varchar2 instead of varchar. Because it treats both null and empty strings as same. Oracle stated that, “Do not use varchar datatype” although currently both used for same purpose. But in future varchar usage may change. It is for future purpose. So now it is better to stick with varchar2.
Difference between varchar and varchar2
| Varchar | Varchar2 |
| 1) Varchar can identify NULL and empty string separately. | 1) Varchar2 cannot identify both separately. Both considered as same for this. |
| 2) Varchar can store minimum 1 and maximum 2000 bytes of character data. | 2) Varchar2 can store minimum 1 and maximum 4000 bytes of character data. |
| 3) Allocate fixed size of data irrespective of the input.
Ex: We defined varchar (15) and entered only 10 characters. But it allocates space for entire 15 characters. |
3) Allocate variable size of data based on input.
Ex: We defined varchar2 (15) and entered only 10 characters. Then varchar2 will allocate space for 10 characters only but not for 15. |
| 4) For varchar data, extra spaces are padded to the right side. | 4) For varchar2 extra spaces will be truncated. |
| 5) Varchar is ANSI Sql standard | 5) Varchar2 is Oracle standard |
| 6) Varchar definition may change in future. | 6) Varchar2 definition will not change. It is standard. |
| 7) Varchar is an external datatype. | 7) Varchar2 is an internal datatype. |
Comment below if you have doubts regarding varchar vs varchar2.
thank u sir for sharing this great article
You have good effert sir
Don’t follow this post. All these information about varchar and varchar2 is wrong. Seems like this guy is not working in real world environment. His full time job is writing blogs that he has no experience at all
The best differentiation I read ever. Well formatted and most precise. I will recommend this to all my trainees.
then u told what is real difference
it was awesome info dear sir thanks alot for such a effective info
thx sir
Excellent and clear and neat difference sir…
declare
lv_1 varchar2(10) := ”;
lv_2 varchar(10) := ”;
lv_3 varchar(10) := ‘a’;
begin
if lv_1 is null then
dbms_output.put_line(‘test–1’);
end if;
if lv_2 is null then
dbms_output.put_line(‘test–2’);
end if;
dbms_output.put_line(length(lv_3));
end;
Can you please explain why its not showing any difference here?
This is not true:
1) Varchar can identify NULL and empty string separately.
The author did not test it.
what is internal data type? and what is external datatype?
thats the real question!!
This post is misleading about varchar and varchar2. Both of these allocates space dynamically. Currently both of them can’t say difference between empty string and null. Only difference between both of them is maximum size Oracle reserved this for future use. Please remove this post as it is a great misleading to the people who are preparing for the interviews. Thank you.
When did Oracle state “Do not use varchar datatype”?
That the ANSI standard VARCHAR would have breaking changes and the Oracle (standard?) VARCHAR2 would not is just a curious statement. Why would you say that?
This is taken from the Oracle documentation:
“VARCHAR Datatype
The VARCHAR datatype is synonymous with the VARCHAR2 datatype. To avoid possible changes in behavior, always use the VARCHAR2 datatype to store variable-length character strings.”
Source: https://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#CNCPT1822
More info:
VARCHAR2 and VARCHAR Datatypes
The VARCHAR2 datatype stores variable-length character strings. When you create a table with a VARCHAR2 column, you specify a maximum string length (in bytes or characters) between 1 and 4000 bytes for the VARCHAR2 column. For each row, Oracle Database stores each value in the column as a variable-length field unless a value exceeds the column’s maximum length, in which case Oracle Database returns an error. Using VARCHAR2 and VARCHAR saves on space used by the table.
For example, assume you declare a column VARCHAR2 with a maximum size of 50 characters. In a single-byte character set, if only 10 characters are given for the VARCHAR2 column value in a particular row, the column in the row’s row piece stores only the 10 characters (10 bytes), not 50.
Oracle Database compares VARCHAR2 values using nonpadded comparison semantics.
VEry nicely differentiated.
THanks for the article.
Don’t follow the post seems the publisher is confused between char, varchar and varchar 2.
Varchar2 supports special characters which varchar wont support
According to this there is no difference between char and varchar. There is so the information is flawed. Don’t send it to you trainees.