muralidhar_batta

 Profile Answers by muralidhar_batta 

• Feb 26th, 2006

 

varchar means fixed length character data(size) ie., min size-1 and max-2000

where as varchar2 means variable length character data ie., min-1 to max-4000

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

durga4578

 Profile Answers by durga4578 Questions by durga4578

• Mar 11th, 2006

 

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.

Culver_lake

 Profile Answers by 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.

praveen_garigipati

 Profile Answers by praveen_garigipati Questions by praveen_garigipati

• Apr 5th, 2006

 

Hi,

i am kumar.can you give the clear differnece between varchar and varchar2 with example.

Thanks&regards

kumar

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??

vijaykantjoshi

 Profile Answers by vijaykantjoshi 

• Apr 21st, 2006

 

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

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

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.

pramod

• May 20th, 2006

 

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

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.

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.

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?

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 )

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 ?

perindev

 Profile Answers by 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....

kashyap_aseem

 Profile Answers by kashyap_aseem 

• Feb 14th, 2008

 

it should be same

vidhyavenkat

 Profile Answers by vidhyavenkat 

• Feb 16th, 2008

 

Both are identical

vidhyavenkat

 Profile Answers by vidhyavenkat 

• Feb 16th, 2008

 

char accepts only characters alone whereas varchar or varchar2 allows alphanumeric values...

mihiradash

 Profile Answers by 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

cherry87

 Profile Answers by 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

anwar_ali

 Profile Answers by 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.

mayur.joshi

 Profile Answers by 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

nareshkumarmca

 Profile Answers by nareshkumarmca 

• Feb 1st, 2010

 

varchar can store up to 2000 bytes
varchar2 can store upto 4000 bytes

vermaabhishek19801

 Profile Answers by vermaabhishek19801 

• Mar 16th, 2010

 

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.

srikar777

 Profile Answers by 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.

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.

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

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.............

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
 


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


 


TABLE created.


 


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


 


1 ROW created.


 


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


 


COL1       LENGTH(COL1) ASCII DUMP


---------- ------------ ------------------------------------------------------------


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


Note: ASCII character 32 IS a blank SPACE.


 


VARCHAR


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


 


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


 


TABLE created.


 


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


 


1 ROW created.


 


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


 


COL1       LENGTH(COL1) ASCII DUMP


---------- ------------ ------------------------------------------------------------


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


VARCHAR2


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


 


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


 


Table created.


 


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


 


1 row created.


 


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


 


COL1       LENGTH(COL1) ASCII Dump


---------- ------------ ------------------------------------------------------------


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

Naresh

• Nov 28th, 2011

 

Thank you..nice explaination..

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.

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.

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,

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.

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

Trupti Das

• Sep 18th, 2014

 

Only difference is size limit.

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.

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.

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.

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?

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.

akhil

• Mar 2nd, 2017

 

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

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.

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.

Prabhat Sahu

 Profile Answers by Prabhat Sahu 

• Oct 30th, 2018

 

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

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.