Q
Problem solve Get help with specific problems with your technologies, process and projects.

VARCHAR2 vs. VARCHAR vs. CHAR

Regarding "Why does Oracle recommend using VARCHAR2 instead of VARCHAR datatypes?", I challenge the answer given. I have worked with Oracle 5, 6, 7, 7.2, 8.0, and 8.1, and although the early versions of Oracle are hard to remember, it has always been true that a zero-length string and NULL are treated the same for the varying-length character data type (VARCHAR2). Perhaps you had the "CHAR" type in mind, which is a fixed-length character data type; here an empty string would be padded with blanks to the declared length, and was definitely not the same as NULL.

You are correct, to a point. I had CHAR in mind, instead of VARCHAR2. Thanks for pointing that out.

But Note:1011340.6 on Metalink does state that the the behavior of zero-length strings and NULLS changed by Oracle 7.1.3 all the way through Oracle 8.1.7. And if you do some further digging, how Oracle handles zero-length strings really only applies to inserting the data. Try issuing the following queries (I tested this in Oracle 8, 8i, and 9i):

SELECT * FROM my_table WHERE column_name IS NULL;
SELECT * FROM my_table WHERE column_name='';
They will return two different results! So not all handling of zero length strings is the same. I recall reading somewhere along the line that Oracle Corp. was going to change this behavior in 9i. Apparently, my information was in error. Thanks for pointing it out!

For More Information


Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close