Difference between VARCHAR and VARCHAR2?

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

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.

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

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.

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.

Hi,

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

Thanks&regards

kumar

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

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

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

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

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.

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

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

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

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

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 )

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 ?

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

it should be same

Both are identical

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

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

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

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.

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

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

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.

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.

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

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.

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

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

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

Thank you..nice explaination..

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.

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.

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,

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.