x002548's Blog

Not Just a Number - Brett Kaiser
posts - 89, comments - 586, trackbacks - 39

Datatypes: CHAR, VARCHAR and VARCHAR2

HUH?  VARCHAR2?

You gotta love Oracle...ANSI be damned.  Well I never specifically knew why we needed to used varchar2 during the last several projects we were on.  Mostly because I was told to...don't think the person who directed the effort really knew either.

CHAR is simple, it retains any data you put in there AND space pads the values.  A pain for comparisons later on.

VARCHAR is not supported and is reserved for future use 

VARCHAR2's definition is a little more complicated since it's different between version (Hey, eve M$ tries to backward compatible)

Brian Peasland at http://searchoracle.techtarget.com puts it best

Quote:

Actually, Oracle Corp. has changed the semantics of the VARCHAR2 datatype. Currently, in Oracle 9i, when you insert a zero length string into a VARCHAR2 column, Oracle treats this the same as if you insert a NULL value into that column. But it wasn't always this way. Previous versions of the database treated zero length strings and NULL values as two distinctly different items.

And aren't they supposed to be different?  This is progress?  An empty string <> null, hell even null <> null..so what gives?  Is an empty string and null ANSI compliance?  Or does it have nothing to do with it?  Ah, more reasearch...

UPDATE:

Tony Andrews, a moderator over at dbforums points out the following...further adding to the confusion...nothing like testing things out...Thanks Tony

SQL*Plus: Release 8.0.6.0.0 - Production on Wed Jan 5 17:27:58 2005

(c) Copyright 1999 Oracle Corporation.  All rights reserved.


Connected to:
Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production
With the Partitioning option
JServer Release 8.1.7.3.0 - Production

SQL> create table t (id int, text varchar2(10));

Table created.

SQL> insert into t values (1, '');

1 row created.

SQL> select * from t where text is null;

        ID TEXT
---------- ----------
         1

And then this

SQL> select * from t where text = '';

no rows selected

Go Figure!


Feel free to use these results in your blog!

Tony Andrews
http://tonyandrews.blogspot.com

Karen Morton's article at the site was also extremely helpful, and there are a lot of other good links

http://searchoracle.techtarget.com/ateQuestionNResponse/0,289625,sid41_cid506468_tax294551,00.html

 

And there's always my simplistic pleas for help

http://www.dbforums.com/showthread.php?p=4030309&posted=1#post4030309

 

Print | posted on Wednesday, January 05, 2005 11:18 AM | Filed Under [ Oracle ]

Feedback

Gravatar

# WTF Trackback

WTF Trackback
9/13/2005 11:58 AM | Pingback/TrackBack
Gravatar

# re: Datatypes: CHAR, VARCHAR and VARCHAR2

cud not get the needed information...
10/27/2005 12:54 PM | karthik
Gravatar

# re: Datatypes: CHAR, VARCHAR and VARCHAR2

Can you tell me wat is the difference b/w Varchar and Varchar 2?
1/27/2006 9:12 PM | Trinity
Gravatar

# re: Datatypes: CHAR, VARCHAR and VARCHAR2

diif bw varchar and varchar2
2/9/2006 4:37 AM | srinath
Gravatar

# re: Datatypes: CHAR, VARCHAR and VARCHAR2

no comments
2/23/2006 5:54 PM | Vlad
Gravatar

# re: Datatypes: CHAR, VARCHAR and VARCHAR2

Is there a way to contenate A LONG field Varchar2 with another??
As in Select a.subject||a.catalog||a.descrlong from XXX.tbl
I am not able to - I end up doing select a.subject||a.catalog,a.descrlong from XXX.tbl.

The problem is that I end up with a ',' and spces which is not acceptable by edits of the target database.

Thanks for you help.
3/20/2006 12:37 PM | Sushama Rath
Gravatar

# re: Datatypes: CHAR, VARCHAR and VARCHAR2

This is a common newbie mistake. When you insert empty strings and 'Select * from xyz where colname=""'
10/11/2006 1:26 AM | Jacob
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET