Re: CHAR vs VARCHAR2

Dear Lee,

I hope this helps

CHAR, VARCHAR and VARCHAR2 are all used for the storage of character data. Each of these datatypes take a parameter representing the width or number of characters. For example, NAME CHAR(7), declares a column or variable called NAME to be of datatype CHAR with up to 7 characters. The parameter is optional and if omitted a value of 1 is assumed.

There are difference between the meanings depending on whether you are using Oracle 6 or Oracle 7. Under Oracle 6, CHAR and VARCHAR both hold character data up to a maximum of 255 characters. These two types contain variable length character strings up to the size specified in the declaration. Under Oracle 7 however, the CHAR datatype represents fixed length character strings and strings which are shorter than the specified length are padded with spaces to make them up to the length specified in the declaration.

The VARCHAR2 datatype, under Oracle 7, represents variable length strings, as used under Oracle 6 which are not padded with spaces. VARCHAR2 can hold up to a maximum of 2000 characters. This implies a problem for conversion between Oracle 6 databases and Oracle 7 databases. For example, given a table called EMPS which contains a column called NAME which is declared to have the datatype CHAR(7), the statement SELECT * FROM EMPS WHERE NAME = 'JONES'; will only retrieve the correct results under Oracle version 6. This is due to the fact that under Oracle version 7 the datatype CHAR is not longer variable length strings but rather fixed length strings padded with spaces. Thus the names JONES has two additional spaces on the end to pad it out to take the full 7 characters.

"Lee Miller" <diamon_at_newsguy.com> wrote in message news:95pctc02j99_at_enews4.newsguy.com...
> Does anyone have a good link to the caveats of using VARCHAR2 in place of
> CHAR, and what general guidelines there are for when you should use which.
>
>
Received on Thu Feb 08 2001 - 03:24:50 CET