What is the major difference between Varchar2 and char

Question

Creating Table:

CREATE TABLE test ( charcol    CHAR(10), varcharcol VARCHAR2(10));  SELECT LENGTH(charcol), LENGTH(varcharcol) FROM test; 

Result:

LENGTH(CHARCOL) LENGTH(VARCHARCOL) --------------- ------------------              10                  1  

Please Let me know what is the difference between Varchar2 and char? At what times we use both?

Answer 1

Although there are already several answers correctly describing the behaviour of char, I think it needs to be said that you should not use it except in three specific situations:

1. You are building a fixed-length file or report, and assigning a non-null value to a char avoids the need to code an rpad() expression. For example, if firstname and lastname are both defined as char(20), then firstname || lastname is a shorter way of writing rpad(firstname,20) || rpad(lastname,20) to create Chuck Norris .
2. You need to distinguish between the explicit empty string '' and null. Normally they are the same thing in Oracle, but assigning '' to a char value will trigger its blank-padding behaviour while null will not, so if it's important to tell the difference, and I can't really think of a reason why it would be, then you have a way to do that.
3. Your code is ported from (or needs to be compatible with) some other system that requires blank-padding for legacy reasons. In that case you are stuck with it and you have my sympathy.

There is really no reason to use char just because some length is fixed (e.g. a Y/N flag or an ISO currency code such as 'USD'). It's not more efficient, it doesn't save space (there's no mythical length indicator for a varchar2, there's just a blank padding overhead for char), and it doesn't stop anyone entering shorter values. (If you enter 'ZZ' in your char(3) currency column, it will just get stored as 'ZZ '.) It's not even backward-compatible with some ancient version of Oracle that once relied on it, because there never was one.

And the contagion can spread, as (following best practice) you might anchor a variable declaration using something like sales.currency%type. Now your l_sale_currency variable is a stealth char which will get invisibly blank-padded for shorter values (or ''), opening the door to obscure bugs where l_sale_currency does not equal l_refund_currency even though you assigned 'ZZ' to both of them.

Some argue that char(n) (where n is some character length) indicates that values are expected to be n characters long, and this is a form of self-documentation. But surely if you are serious about a 3-character format (ISO-Alpha-3 country codes rather than ISO-Alpha-2, for example), wouldn't you define a constraint to enforce the rule, rather than letting developers glance at a char(3) datatype and draw their own conclusions?

CHAR was introduced in Oracle 6 for, I'm sure, ANSI compatibility reasons. Probably there are potential customers deciding which database product to purchase and ANSI compatibility is on their checklist (or used to be back then), and CHAR with blank-padding is defined in the ANSI standard, so Oracle needs to provide it. You are not supposed to actually use it.

Answer 2

Simple example to show the difference:

SELECT      '"'||CAST('abc' AS VARCHAR2(10))||'"',      '"'||CAST('abc' AS CHAR(10))||'"'  FROM dual;   '"'||CAST('ABC'ASVARCHAR2(10))||'"' '"'||CAST('ABC'ASCHAR(10))||'"' ----------------------------------- ------------------------------- "abc"                               "abc       "                    1 row selected. 

The CHAR is usefull for expressions where the length of charaters is always fix, e.g. postal code for US states, for example CA, NY, FL, TX