Difference between VARCHAR and VARCHAR2?

Editorial / Best Answer

Answered by: Ranjeet

  • Feb 27th, 2006


Emp_name varchar(10) -  if you enter value less than 10 then remaining space can not be deleted. it used total 10 spaces.

Emp_name varchar2(10) - if you enter value less than 10 then remaining space is automatically deleted

Showing Answers 1 - 50 of 50 Answers

Ranjeet

  • Feb 27th, 2006
 

The difference between Varchar and Varchar2 is both are variable length but only 2000 bytes of character of data can be store in varchar where as 4000 bytes of character of data can be store in varchar2.

Ranjeet

  • Feb 27th, 2006
 

Emp_name varchar(10) -  if you enter value less than 10 then remaining space can not be deleted. it used total 10 spaces.

Emp_name varchar2(10) - if you enter value less than 10 then remaining space is automatically deleted

VARCHAR : when u r using this data type what ever size u r giving that much of memory is allocated to that variable. suppose u r giving varcahr(20) then u enter only 15 cahracters of that variable then also it is giving memory of 20 cahracters.VARCHAR2: when u r using this data type the memory allocation is different. suppose u r givng varchar2(20) then it is allocated the memory for 20 cahracter. but the varibale is giving 15 character then the memory willbe allocated only that 15 characters not for 20 cahracters.this is the main difference between varcahr and varchar2

neeharika

  • Mar 18th, 2006
 

VARCHAR is generally used for sql where as VARCHAR2 is used for the Mysql.literally both mean the same i.e they accept both numericals and variables.

  Was this answer useful?  Yes

Culver_lake

  • Mar 18th, 2006
 

I'm sorry but these answers are all off the mark.  VARCHAR2 is Oracle and VARCHAR is ansi standard. Regardless how they behave or how big it can be it's simply a vendor vs standard issue.

Tatyana

  • Apr 5th, 2006
 

Sorry guys,

There is a quotation from Christopher Allen ?Oracle Database 10g, PL/SQL 101?:

?There is a datatype named VARCHAR as well. In early versions of Oracle the maximum length of a VARCAR column was 1,000 characters, later expanded to 4,000 characters for the datatype VARCHAR2. In current version of Oracle, both datatypes enjoy 4,000-character limit. However Oracle Corporation says they may change the behavior of a VARCHAR column in future ? and we have no way of knowing how the ?changed? VARCHAR will behave ? so??

  Was this answer useful?  Yes

There is no difference between varchar & varchar2 regarding memory allocation & release. both r variable in length and occupies memory at runtime.

Ashok Verma............

  Was this answer useful?  Yes

Ankur Srivastava

  • Apr 26th, 2006
 

Varchar2 is variable width character data type, so if you define column with width 20 and insert only one character to tis column only, one character will be stored in database. Char is not variable width so when you define column with width 20 and insert one character to this column it will be right padded with 19 spaces to desired length, so you will store 20 characters in the dattabase .Varchar data type from Oracle 9i is automaticlly promoted to varchar2

Sandeep kumar H.B.

  • Apr 28th, 2006
 

varchar is of fixed size where as varchar2 is of variable where varhcar ranges from 1 to 2000 characters and varchar2 ranges from 1 to 4000 characters.

  Was this answer useful?  Yes

pramod

  • May 20th, 2006
 

varchar and varchar2 both are fixed length character but varchar can accept upto 2000 where varchar2 can accept upto 4000.

  Was this answer useful?  Yes

Walid Garosh

  • Jul 13th, 2006
 

The first supports english charset, while the later supports non-english charsets including asian laguages which require 2 chars to store one character.

  Was this answer useful?  Yes

shyamprasad

  • Jul 26th, 2006
 

Hi All,Currently VARCHAR behaves exactly the same as VARCHAR2. However, this type should not be used as it is reserved for future usage.

  Was this answer useful?  Yes

Lopex

  • Mar 9th, 2007
 

Ask five experts and get six opinions. This forum is a prime example. Why not first reading a bit and then answering?

  Was this answer useful?  Yes

Himadri Sekhar Das

  • Mar 19th, 2007
 

i know the difference of varchar and char

"varchar2" deletes the redidual spaces from the allocated space (if the additional space is not required after allocating the variable )


"char" does not deletes the redidual spaces from the allocated space (if the additional space is not required after allocating the variable )

  Was this answer useful?  Yes

priya

  • Jun 10th, 2007
 

I am trying to extract a varchar column value from sqlserver2005 and place it in oracle 9i. It returns an error saying mismatch in the data type. What could be the reason for this ?

  Was this answer useful?  Yes

perindev

  • Feb 4th, 2008
 


still, I do not find a proper answer. I do not find any difference between varchar and varchar(2)...both occupies 4000 character memory allocations.

Is there any other difference apart from this?

Can any body let me know how much char data type in oracle occupies?

whats the difference between char, varchar, varchar2 ?????????? is a BIG QUESTION BEFORE ME......any body please help....

  Was this answer useful?  Yes

mihiradash

  • May 23rd, 2008
 

Accoring to your answar i tried the following example

CREATE TABLE sample
(
  col1 VARCHAR(2500)
)
but there is no error, then how come do you say that the maximum length is 2000 in varchar

please answar me back

  Was this answer useful?  Yes

cherry87

  • Jan 19th, 2009
 

varchar2 is a variable width character datatype, i.e when this data type is selected and if we specify the column width to be as 15 and if we insert 3 characters in to this column, then only 3 characters are stored in the database(db). But, char is not like that if we specify the column width to be as 20 as if we insert only 2 characters in that column then, it will be right padded with 18 spaces to acquire the desired length in the Database. So, we would be storing all 20 characters in the database.

Regards,
Cherry

  Was this answer useful?  Yes

anwar_ali

  • Jan 31st, 2009
 

In varchar we cannot have space alloted in the memory allocated and once it is allocated we cannot re-enter the size again but in varchar2 we can change the space allocated whenever we want.

  Was this answer useful?  Yes

mayur.joshi

  • Nov 16th, 2009
 

varchar and varchar2 are both data types in SQL (Oracle, MySQL, Microsoft SQL
Server, DB2) which accept numeric as well as alphabetic values. Only difference
is that varchar2 reduces wastage of space as it sets the length of character
during run time.


Eg: name varchar2(10) - 10 being max no of characters that can be entered and
not the character length of the value.


varchar sets the character length to what is specified during declaration and
uses the entire space even if we do not enter that many characters


Eg: varchar(10) - 10 is the default character length


  Was this answer useful?  Yes

One Important difference is that nvarchar allows unicode storing where as varchar stores only non unicode data.  For example if we are using varchar than we can use string characters in english only but if nvarchar then along with of english any  language can be used.

  Was this answer useful?  Yes

srikar777

  • May 24th, 2010
 

NOTE about 'VARCHAR ' from Oracle docs:
VARCHAR Datatype:
Do not use the VARCHAR datatype. Use the VARCHAR2 datatype instead. Although the
VARCHAR datatype is currently synonymous with VARCHAR2, the VARCHAR datatype is
scheduled to be redefined as a separate datatype used for variable-length character
strings compared with different comparison semantics.

prakash

  • Jul 12th, 2011
 

No. this answer is entirely wrong. We can change the size after it has been created.

  Was this answer useful?  Yes

ochani monica

  • Sep 6th, 2011
 

1.VARCHAR can store up to 2000 bytes of characters while VARCHAR2 can store up to 4000 bytes of characters.

2.If we declare datatype as VARCHAR then it will occupy space for NULL values, In case of VARCHAR2 datatype it will not occupy any space.

  Was this answer useful?  Yes

Deepika Srivastava

  • Sep 7th, 2011
 

VARCHAR -> it does not return back to DB extra memory of variable by passing value of less size
for ex. DECLARE @Value VARCHAR(10)
SET @Value='Hello'
here passing value size is 5 and assigning size is 10 so remain 10-5=5 will waste


VARCHAR2 -> it returns back to DB extra memory of variable by passing value of less size
for ex. DECLARE @Value VARCHAR2(10)
SET @Value='Hello'
here passing value size is 5 and assigning size is 10 so remain 10-5=5 will return back free to DB for another use

  Was this answer useful?  Yes

KUMARESH R

  • Oct 23rd, 2011
 

desc out_team1;
Name
----------
COLL1
COLL2
COLL3


SELECT COLL1, length(COLL1), dump(COLL1) "ASCII" FROM OUT_TEAM1;


COLL1 LENGTH(COLL1) ASCII
---------- ------------- ----------------------------------------------------
10 10 Typ=96 Len=10: 49,48,32,32,32,32,32,32,32,32
1 10 Typ=96 Len=10: 49,32,32,32,32,32,32,32,32,32
1000 10 Typ=96 Len=10: 49,48,48,48,32,32,32,32,32,32
1 10 Typ=96 Len=10: 49,32,32,32,32,32,32,32,32,32
1000 10 Typ=96 Len=10: 49,48,48,48,32,32,32,32,32,32


SELECT COLL2, length(COLL2), dump(COLL2) "ASCII" FROM OUT_TEAM1;


COLL2 LENGTH(COLL2) ASCII
---------- ------------- --------------------------------------------------------------------
20 2 Typ=1 Len=2: 50,48
2 1 Typ=1 Len=1: 50
20000 5 Typ=1 Len=5: 50,48,48,48,48
20 2 Typ=1 Len=2: 50,48
20000 5 Typ=1 Len=5: 50,48,48,48,48

Elapsed: 00:00:00.00

SELECT COLL3, length(COLL3), dump(COLL3) "ASCII" FROM OUT_TEAM1;


COLL3 LENGTH(COLL3) ASCII
---------- ------------- -------------------------------------
30 2 Typ=1 Len=2: 51,48
3 1 Typ=1 Len=1: 51
300000 6 Typ=1 Len=6: 51,48,48,48,48,48
300 3 Typ=1 Len=3: 51,48,48
30000 5 Typ=1 Len=5: 51,48,48,48,48

SELECT * FROM OUT_TEAM1;


COLL3 LENGTH(COLL3) ASCII
---------- ------------- --------------------------------------
30 2 Typ=1 Len=2: 51,48
3 1 Typ=1 Len=1: 51
300000 6 Typ=1 Len=6: 51,48,48,48,48,48
300 3 Typ=1 Len=3: 51,48,48
30000 5 Typ=1 Len=5: 51,48,48,48,48


since varchar & varchar2 are similer but their memory ay varies.............

  Was this answer useful?  Yes

sunil rana

  • Nov 13th, 2011
 

CHAR
should be used for storing fix length character strings. String values will be space/blank padded before stored on disk. If this type is used to store variable length strings, it will waste a lot of disk space.

Code
  1.  

  2. SQL> CREATE TABLE char_test (col1 CHAR(10));

  3.  

  4. TABLE created.

  5.  

  6. SQL> INSERT INTO char_test VALUES ('qwerty');

  7.  

  8. 1 ROW created.

  9.  

  10. SQL> SELECT col1, LENGTH(col1), DUMP(col1) "ASCII Dump" FROM char_test;

  11.  

  12. COL1       LENGTH(COL1) ASCII DUMP

  13. ---------- ------------ ------------------------------------------------------------

  14. qwerty               10 Typ=96 Len=10: 113,119,101,114,116,121,32,32,32,32

  15. Note: ASCII character 32 IS a blank SPACE.

  16.  

  17. VARCHAR

  18. Currently VARCHAR behaves exactly the same AS VARCHAR2. However, this TYPE should NOT be used AS it IS reserved FOR future usage.

  19.  

  20. SQL> CREATE TABLE varchar_test (col1 VARCHAR2(10));

  21.  

  22. TABLE created.

  23.  

  24. SQL> INSERT INTO varchar_test VALUES ('qwerty');

  25.  

  26. 1 ROW created.

  27.  

  28. SQL> SELECT col1, LENGTH(col1), DUMP(col1) "ASCII Dump" FROM varchar_test;

  29.  

  30. COL1       LENGTH(COL1) ASCII DUMP

  31. ---------- ------------ ------------------------------------------------------------

  32. qwerty                6 Typ=1 Len=6: 113,119,101,114,116,121

  33. VARCHAR2

  34. VARCHAR2 IS used TO store variable LENGTH character strings. The string VALUE's length will be stored on disk with the value itself.

  35.  

  36. SQL> CREATE TABLE varchar2_test (col1 VARCHAR2(10));

  37.  

  38. Table created.

  39.  

  40. SQL> INSERT INTO varchar2_test VALUES ('qwerty');

  41.  

  42. 1 row created.

  43.  

  44. SQL> SELECT col1, length(col1), dump(col1) "ASCII Dump" FROM varchar2_test;

  45.  

  46. COL1       LENGTH(COL1) ASCII Dump

  47. ---------- ------------ ------------------------------------------------------------

  48. qwerty                6 Typ=1 Len=6: 113,119,101,114,116,121

  Was this answer useful?  Yes

Naresh

  • Nov 28th, 2011
 

Thank you..nice explaination..

  Was this answer useful?  Yes

praveen gupta

  • Dec 2nd, 2011
 

varchar2 is varable length if you want to insert a name in a table you define a varchar2(50) char your insert a name only 5 char your table space is not waist your table space will be free but you define a varchar your space
will not free store a your name and blank space is store.

  Was this answer useful?  Yes

gangi reddy

  • Dec 21st, 2011
 

Difference b/w varchar and varchar2 is varchar is ANSI standard and varchar2 is a Oracle data type
and also varchar is fixed length record, varchar2 is variable length.

  Was this answer useful?  Yes

pooja manoja

  • Feb 4th, 2012
 

Varchar - If we declare datatype as varchar then it will occupy space for null values. It can store upto 2000 bytes of character.

Varchar2 - If we declare datatype as varchar2, it will not occupy any space. It can store 4000 bytes of character.

Thanx,

  Was this answer useful?  Yes

Ravi

  • Jul 13th, 2012
 

Varchar & Varchar2 both having the same size, & working as same.
Bt the main diff is varchar is an ANSI Datatype & Varchar2 is the Oracle datatype.

  Was this answer useful?  Yes

manojkumarkadiyala

  • Jul 10th, 2014
 

Varchar is nothing but fixed length character data size min1 to max 2000
varchar2 is nothing but variable character data size min 1 to max 4000

  Was this answer useful?  Yes

Trupti Das

  • Sep 18th, 2014
 

Only difference is size limit.

  Was this answer useful?  Yes

jamie

  • Apr 9th, 2015
 

It bugs me that so many of the answers above erroneously say that varchar is a fixed-length field.
Varchar has always been a variable-length field. Thats what the "var" in the name is for. It was named to distinguish it from char, which IS fixed-length.
The answers above that explain that varchar and varchar2 are currently synonymous in Oracle, but varchar is deprecated, are correct.

  Was this answer useful?  Yes

vasu

  • Dec 11th, 2015
 

Varchar - If we declare datatype as varchar then it will occupy space for null values. It can store upto 2000 bytes of character.
Varchar2 - If we declare datatype as varchar2, it will not occupy any space. It can store 4000 bytes of character.

  Was this answer useful?  Yes

venkatesh

  • Feb 3rd, 2016
 

VARCHAR is reserved by Oracle to support distinction between NULL and empty string in future, as ANSI standard prescribes. VARCHAR2 does not distinguish between a NULL and empty string, and never will. If you rely on empty string and NULL being the same thing, you should use VARCHAR2.

  Was this answer useful?  Yes

lakshmi

  • Mar 2nd, 2016
 

ename varchar (10) if we entered less than that mentioned size of memory it will automatically takes 10 bytes.
ename varchar2 (10) if we entered values with less than that size it will takes only required length of bytes. Then what is diff between varchar and char?

  Was this answer useful?  Yes

Vaibhav Vijay

  • Mar 19th, 2016
 

VARCHAR is used to store values dynamically (while CHAR static). So, to calculate the memory space we represent the VARCHAR2 where 2 represents the 2 bytes of memory used to calculate the storage space required at the assignment time.

  Was this answer useful?  Yes

akhil

  • Mar 2nd, 2017
 

If so what is the difference between Varchar and Char.
As per your explanation varchar similar to char

  Was this answer useful?  Yes

Soniya

  • May 17th, 2017
 

VARCHAR: Variable-length, non-Unicode character data. The database collation determines which code page the data is stored using.
NVARCHAR: Variable-length Unicode character data. Dependent on the database collation for comparisons.

  Was this answer useful?  Yes

prasanth

  • May 12th, 2018
 

Both varchar and Varchar2 use 4000 bytes.
Varchar treats null and empty string as different.
Varchar2 consider noth null and empty string as same thing.

  Was this answer useful?  Yes

CHARACTER Datatype:
CHAR, VARCHAR both are of CHARACTER Datatype in oracle.
CHAR is fixed length datatype and VARCHAR is Variable length datatype to store character data. i.e. A-Z , a-z , 0-9 and keyboard characters etc.
Default size is 1 character and it can store maximum up to 2000 bytes.
Example : EName, EmpID, PassportNo, SSN, etc.
If we define a variable or column of a table as CHAR(10) and assign a value ’TOM’ to that then the remaining 7 space after the character string will be remain un usable by other data operation. whereas if we define a VARIABLE or COLUMN of a table as VARCHAR(10) and assign a value ’TOM’ to that then the remaining 7 space after the character string we can use for other data operation.

Example:
EName CHAR(10) := ‘TOM’; westage of 7 space after the string
EName VARCHAR(10) := ‘TOM’; Spaces can be Reuse which left after the string

  Was this answer useful?  Yes

Krishna Kant Kumar

  • Mar 12th, 2019
 

VARCHAR is going to be replaced by VARCHAR2 in next version. So, Oracle suggests the use VARCHAR2 instead of VARCHAR while declaring data type.

VARCHAR can store up to 2000 bytes of characters while VARCHAR2 can store up to 4000 bytes of characters.

If we declare data type as VARCHAR then it will occupy space for NULL values, In case of VARCHAR2 data type it will not occupy any space.

VARCHAR is of ANSI SQL standard while Varchar2 is of Oracle standard.

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions