What is the major difference between Varchar2 and

问题:

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?

回答1:

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

回答2:

This is an old thread, but it just came top of a Google search for \'Oracle char vs varchar2\', and while 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 two 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).
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.

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.

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.

回答3:

CHAR type has fixed size, so if you say it is 10 bytes, then it always stores 10 bytes in the database and it doesn\'t matter whether you store any text or just empty 10 bytes

VARCHAR2 size depends on how many bytes you are actually going to store in the database. The number you specify is just the maximum number of bytes that can be stored (although 1 byte is minimum)

You should use CHAR when dealing with fixed length strings (you know in advance the exact length of string you will be storing) - database can then manipulate with it better and faster since it knows the exact lenght

You should use VARCHAR2 when you don\'t know the exact lenght of stored strings.

Situation you would use both may be:

name VARCHAR2(255),
zip_code CHAR(5) --if your users have only 5 place zip codes

回答4:

Just to avoid confusion about much wrong information. Here are some information about difference including performance

Reference: https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2668391900346844476

Since a char is nothing more than a VARCHAR2 that is blank padded out to the maximum length - that is, the difference between the columns X and Y below:

create table t ( x varchar2(30), y char(30) ); insert into t (x,y) values ( rpad(\'a\',\' \',30), \'a\' );

IS ABSOLUTELY NOTHING, and given that the difference between columns X and Y below:

insert into t (x,y) values (\'a\',\'a\')

is that X consumes 3 bytes (null indicator, leading byte length, 1 byte for \'a\') and Y consumes 32 bytes (null indicator, leading byte length, 30 bytes for \'a \' )

Umm, varchar2 is going to be somewhat \"at an advantage performance wise\". It helps us NOT AT ALL that char(30) is always 30 bytes - to us, it is simply a varchar2 that is blank padded out to the maximum length. It helps us in processing - ZERO, zilch, zippo.

Anytime you see anyone say \"it is up to 50% faster\", and that is it - no example, no science, no facts, no story to back it up - just laugh out loud at them and keep on moving along.

There are other \"made up things\" on that page as well, for example:

\"Searching is faster in CHAR as all the strings are stored at a specified position from the each other, the system doesnot have to search for the end of string. Whereas in VARCHAR the system has to first find the end of string and then go for searching.\"

FALSE: a char is just a varchar2 blank padded - we do not store strings \"at a specified position from each other\". We do search for the end of the string - we use a leading byte length to figure things out.

回答5:

CHAR

CHAR should be used for storing fix length character strings. String values will be space/blank padded before stored on disk. If this type is used to store varibale length strings, it will waste a lot of disk space.

VARCHAR2

VARCHAR2 is used to store variable length character strings. The string value\'s length will be stored on disk with the value itself.

And

At what times we use both?

Its all depend upon your requirement.

回答6:

CHAR is used for storing fix length character strings. It will waste a lot of disk space if this type is used to store varibale length strings.

VARCHAR2 is used to store variable length character strings.

At what times we use both?

This may vary and depend on your requirement.

EDIT:-

Lets understand this with an example, If you have an student name column with size 10; sname CHAR(10) and If a column value \'RAMA\' is inserted, 6 empty spaces will be inserted to the right of the value. If this was a VARCHAR column; sname VARCHAR2(10). Then Varchar will take 4 spaces out of 10 possible and free the next 6 for other usage.