What is the difference between varchar and varchar2 in Oracle?

Question

What is the difference between varchar and varchar2?

What are the odds that this question was inspired by this blog post? http://www.joelonsoftware.com/articles/GuerrillaInterviewing3.html — Joshua Snider, Jun 14 '15 at 18:59

score 399 · Accepted Answer · edited Jan 08 '16 at 22:31

399

As for now, they are synonyms.

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.

edited Jan 08 '16 at 22:31

MK.

33,605
18
74
111

answered Jul 23 '09 at 11:41

Quassnoi

413,100
91
616
614

49

Hadn't heard that rationale before, that's useful. Thanks. For the record, it is still totally ridiculous that the main character type in Oracle is "varchar2". Doesn't that strike anybody else as a terrible kludge? Seems like how I would have solved some problem in my first week of learning to program. – Ian Varley May 31 '10 at 15:14
9

@Ian: main type is `VARCHAR2` because currently there is no type that behaves like `VARCHAR` should. In fact, you should not use `VARCHAR` at all until it's implemented properly. – Quassnoi May 31 '10 at 15:28
12

Thanks, @Quassnoi. So I guess the stupid part is that Oracle doesn't have a proper VARCHAR like every other database, then? There's something stupid going on here, I'm sure of it ... :) – Ian Varley Jun 01 '10 at 17:48
11

@Ian: when Oracle was being developed, there were no standards. By the time the standards emerged it already had a burden of legacy apps. We all know how it happens. – Quassnoi Jun 01 '10 at 18:30
@Quassnoi I'm using Oracle12c. And I think varchar2 is now making difference between empty and `null` because I tried ` where name is null ` I got null result but when I tried ` where name=''` nothing to show. – Asif Mushtaq Dec 08 '15 at 17:15
1

This doesn't appear to be true? Is there a link to some Oracle document which confirms this?? – MK. Jan 08 '16 at 17:14
@mk: appears quite true to me. There should definitely be some kind of a document or something. – Quassnoi Jan 08 '16 at 18:46
1

@Quassnoi well i agree it _appears_ very plausible but that is not really good enough is it? – MK. Jan 08 '16 at 22:07
@mk: good enough for what? (and, just out of curiosity, why do you add question marks to affirmative statements?) – Quassnoi Jan 08 '16 at 22:24
Oh, wait you don't claim that there is difference, you claim there might be difference in the future. nevermind. – MK. Jan 08 '16 at 22:30
@mk: they claim it, I just repeat after them – Quassnoi Jan 08 '16 at 22:34
3

@quassnoi, that is not correct. Oracle 12c **does not distinguish** `NULL` from `''`. The problem is that `where name=''` is equivalent to `where name = NULL`, and the `=` operator is *always* false for `NULL`—and hence always false for `''` as well. – Dan Lenski Apr 08 '17 at 17:30
5

Sorry, it was actually @UnKnown who wrote the [incorrect comment I was responding to](http://stackoverflow.com/questions/1171196/what-is-the-difference-between-varchar-and-varchar2/1171200?noredirect=1#comment56070615_1171200). The fact that `where x is NULL` returns different results from `where x = ''` does **not** mean that `NULL` and `''` are in any way different. The different behavior is due to the `=` operator. – Dan Lenski Apr 08 '17 at 22:02
I don't see how a separate datatype would be sufficient to support ANSI behaviour. Additional database/session level configuration would still be needed to describe how string literals should behave i.e. should '' be considered a VARCHAR2 or VARCHAR. And if you have a database configuration then what use is a separate datatype? You can infer the literal datatype from the column in will it will be stored in simple case but that's not always the case. – Sebastien Nov 06 '17 at 09:30
There is an ehnancement request to support ANSI behaviour in oracle here https://community.oracle.com/ideas/19866 – Sebastien Nov 06 '17 at 10:57
*VARCHAR is reserved by Oracle to support distinction between NULL and empty string in future, as ANSI standard prescribes.* – could you please provide any reference for this statement? – Piotr Dobrogost Oct 15 '18 at 11:10
@piotrdobrogost: https://docs.oracle.com/cd/A57673_01/DOC/server/doc/SCN73/ch6.htm#varchar2 _The VARCHAR datatype is currently synonymous with the VARCHAR2 datatype. However, in a future version of Oracle, the VARCHAR datatype might store variable-length character strings compared with different comparison semantics. Therefore, use the VARCHAR2 datatype to store variable-length character strings._ – Quassnoi Oct 15 '18 at 16:10
Thanks, but there's nothing about NULL and empty string there. – Piotr Dobrogost Oct 15 '18 at 20:00
@PiotrDobrogost: oh sorry, I thought you wanted a reference to the "future" part. They are old-school lawyered up business, so I don't think you can find an explicit reference to the "different comparison semantics" they are going to introduce before they actually did. However it's a safe bet to assume that those are gonna imply standard compatibility at least to a certain extent, and this thing is the one most major violation of all the SQL standards there are. – Quassnoi Oct 15 '18 at 20:52

score 46 · Answer 2 · edited Jan 25 '19 at 11:19

46

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

Taken from: Difference Between CHAR, VARCHAR, VARCHAR2

edited Jan 25 '19 at 11:19

bugybunny

544
5
13

answered Jul 23 '09 at 11:43

Brian

2,253
2
23
39

@PhilipRego `VARCHAR` should not be used. I edited it – bugybunny Jan 25 '19 at 10:37

sandman · Answer 3 · 2017-09-01T16:45:25.810

Taken from the latest stable Oracle production version 12.2: Data Types

The major difference is that VARCHAR2 is an internal data type and VARCHAR is an external data type. So we need to understand the difference between an internal and external data type...

Inside a database, values are stored in columns in tables. Internally, Oracle represents data in particular formats known as internal data types.

In general, OCI (Oracle Call Interface) applications do not work with internal data type representations of data, but with host language data types that are predefined by the language in which they are written. When data is transferred between an OCI client application and a database table, the OCI libraries convert the data between internal data types and external data types.

External types provide a convenience for the programmer by making it possible to work with host language types instead of proprietary data formats. OCI can perform a wide range of data type conversions when transferring data between an Oracle database and an OCI application. There are more OCI external data types than Oracle internal data types.

The VARCHAR2 data type is a variable-length string of characters with a maximum length of 4000 bytes. If the init.ora parameter max_string_size is default, the maximum length of a VARCHAR2 can be 4000 bytes. If the init.ora parameter max_string_size = extended, the maximum length of a VARCHAR2 can be 32767 bytes

The VARCHAR data type stores character strings of varying length. The first 2 bytes contain the length of the character string, and the remaining bytes contain the string. The specified length of the string in a bind or a define call must include the two length bytes, so the largest VARCHAR string that can be received or sent is 65533 bytes long, not 65535.

A quick test in a 12.2 database suggests that as an internal data type, Oracle still treats a VARCHAR as a pseudotype for VARCHAR2. It is NOT a SYNONYM which is an actual object type in Oracle.

SQL> select substr(banner,1,80) from v$version where rownum=1;
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production    

SQL> create table test (my_char varchar(20));
Table created.

SQL> desc test
Name                 Null?    Type
MY_CHAR                       VARCHAR2(20)

There are also some implications of VARCHAR for ProC/C++ Precompiler options. For programmers who are interested, the link is at: Pro*C/C++ Programmer's Guide

Yes. The discussion of internal vs external types above is from the OCI reference. The [12.2 SQL Language Reference](http://docs.oracle.com/database/122/SQLRF/Data-Types.htm#GUID-DF7E10FC-A461-4325-A295-3FD4D150809E) still carries the same 'do not use' statement it has always had for VARCHAR. — William Robertson, Sep 02 '17 at 08:07
This is why I discussed the OCI differences, otherwise it's pretty much reserved for future usage or as they say 'variable length' which might be hinting at character sets such as multi-byte UTF8... — sandman, Sep 03 '17 at 14:21
You showed that values declared as VARCHAR are stored in the database as VARCHAR2 however VARCHAR's max length (65533 bytes) is greater than VARCHAR2's max length (32767 bytes). How does database handle this? — Piotr Dobrogost, Oct 15 '18 at 20:21
IIRC, long ago (Maybe Oracle 6?) there was a VARCHAR type that misbehaved wrt to space padding - or was it truncating spaces? AAR, VARCHAR2 was the answer. Oracle has been saying they'll bring back VARCHAR but, I'm sure, there will be gnashing of teeth when the empty string IS NOT NULL. — J M D, Nov 05 '19 at 17:54

score 4 · Answer 4 · answered Sep 01 '17 at 22:33

After some experimentation (see below), I can confirm that as of September 2017, nothing has changed with regards to the functionality described in the accepted answer:-

Rextester demo for Oracle 11g: Empty strings are inserted as NULLs for both VARCHAR and VARCHAR2.
LiveSQL demo for Oracle 12c: Same results.

The historical reason for these two keywords is explained well in an answer to a different question.

Palak Jain · Answer 5 · 2017-04-27T20:50:35.910

VARCHAR can store up to 2000 bytes of characters while VARCHAR2 can store up to 4000 bytes of characters.
If we declare datatype as VARCHAR then it will occupy space for NULL values. In the case of VARCHAR2 datatype, it will not occupy any space for NULL values. e.g.,

name varchar(10)

will reserve 6 bytes of memory even if the name is 'Ravi__', whereas

name varchar2(10)

will reserve space according to the length of the input string. e.g., 4 bytes of memory for 'Ravi__'.

Here, _ represents NULL.

NOTE: varchar will reserve space for null values and varchar2 will not reserve any space for null values.

I think this answer is confusing `VARCHAR` with `CHAR`. – Jon Heller Apr 27 '17 at 20:28 — Jon Heller, Apr 27 '17 at 20:28

score -1 · Answer 6 · edited Jul 16 '18 at 10:52

-1

Currently, they are the same. but previously

Somewhere on the net, I read that,

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.

Also,

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

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

edited Jul 16 '18 at 10:52

taras

6,566
10
39
50

answered Jul 16 '18 at 09:43

Ramkumar P

123
1
8

I stumbled upon this post recently, please note that it is incorrect. Execute the following and both fields will be 3 characters: `create table deleteme_table(v varchar(10), v2 varchar2(10)); insert into deleteme_table (v, v2) values ('abc','abc'); select v, length(v), v2, length(v2) from deleteme_table;` – Brian Leach Jun 10 '20 at 19:36
@BrianLeach: Yes, they currently behave the same, but Oracle WILL change stuff in the future to behave like the standard says they should behave. – Daniel Nov 08 '21 at 08:12

What is the difference between varchar and varchar2 in Oracle?

6 Answers6

Linked