Home sql Difference between VARCHAR and VARCHAR2

Difference between VARCHAR and VARCHAR2

Author

Date

Category

Anyone who has dealt with the Oracle database knows to declare character fields and variables as VARCHAR2 , not VARCHAR .

But these data types are kind of synonyms:

CREATE TABLE table1 (
 id number,
 text VARCHAR (10),
 text2 VARCHAR2 (10)
);
Name Null Type
----- ---- ------------
ID NUMBER
TEXT VARCHAR2 (10)
TEXT2 VARCHAR2 (10)

So what’s the difference between them?


Answer 1, authority 100%

They are currently synonymous.

VARCHAR is reserved by Oracle to support, in the future , the difference between NULL and an empty string as described in ANSI .

VARCHAR2 does not distinguish between NULL and an empty string, and never will.

If you are relying on the empty string and NULL to be the same, use VARCHAR2 .

translation of the answer @Quassnoi


Answer 2, authority 75%

Currently, no difference. The type VARCHAR is an alias for VARCHAR2 and will be replaced with VARCHAR2 when declared.

VARCHAR is reserved for use in future releases for ANSI / ISO-SQL compliance and Oracle does not recommend this type. Therefore, everyone uses the VARCHAR2 type because its behavior will never change.

What is the difference between VARCHAR2 and the standard, there are two of them:

  • empty string is interpreted as NULL with length NULL (not 0)

  • NULL is ignored during string concantation, in the standard the result of such concantation will be NULL

Why did this happen (I will paraphrase @JustinCave ):

Oracle is a very old vendor in the database market. Back in the days before SQL was standardized, Oracle theorists made the design decision that empty strings in database fields contain no information and are essentially NULL .
Over time, when the SQL standard appeared and it was decided that NULL and an empty string are essentially different, there were already many users with old code that did not take this difference into account. So Oracle was presented with a choice to invalidate existing code or violate the SQL standard. A less destructive decision was made, according to Oracle , to reserve the VARCHAR datatype to be modified to conform to the SQL standard and introduce the new VARCHAR2 datatype, immutability whose behavior is guaranteed.

When will the intended behavior changes be made to VARCHAR ?

This was in version 7 , note the phrase – “in the next version of Oracle7”:

The VARCHAR datatype is currently synonymous with the VARCHAR2 datatype. It is recommended that you use VARCHAR2 rather than VARCHAR. In a future version of Oracle7, VARCHAR might be a separate datatype used for variable length character strings compared with different comparison semantics.

And, more than 20 years later, in release 19c :

Do not use the VARCHAR data type. Use the VARCHAR2 data type instead. Although the VARCHAR data type is currently synonymous with VARCHAR2, the VARCHAR data type is scheduled to be redefined as a separate data type used for variable-length character strings compared with different comparison semantics.


Answer 3

Correct answer: they do not differ for use; during storage, VARCHAR2 does not reserve space for NULL values, unlike VARCHAR, i.e. for storage (this is important for admins). See the answer at https://stackoverflow.com/questions/ 1171196 / what-is-the-difference-between-varchar-and-varchar2-in-oracle is not correct. There is no difference in working with NULL for VARCHAR or VARCHAR2 (in the future there may be a difference between VARCHAR and VARCHAR2, but now it really is not). The following example illustrates the same behavior:

DECLARE
 l_str VARCHAR (10): = '';
 l_STR2 VARCHAR2 (10): = '';
BEGIN
  IF l_str IS NULL THEN
     dbms_output.put_line ('l_str is null');
    ELSE
     dbms_output.put_line ('l_str is not null');
  END IF;
  IF l_str2 IS NULL THEN
     dbms_output.put_line ('l_str2 is null');
    ELSE
     dbms_output.put_line ('l_str2 is not null');
  END IF;
END;

Programmers, Start Your Engines!

Why spend time searching for the correct question and then entering your answer when you can find it in a second? That's what CompuTicket is all about! Here you'll find thousands of questions and answers from hundreds of computer languages.

Recent questions