The difference and example between char and varchar in Oracle

1. The length of char is fixed, the length of the VARCHAR2 can vary, for example, the storage string "abc", for Char (20), which means that the characters you store will occupy 20 bytes (including 17 null characters), in the database it is occupied by space, and the same VARCHAR2 (20) Only occupies 3 bytes of length, 20 is the maximum value, when you store the characters less than 20 o'clock, stored by the actual length.

The efficiency of 2.CHAR is slightly higher than that of VARCHAR2. It seems cscm_number should be set to char (19)

3. The current varchar is synonymous with VARCHAR2. Industry-standard varchar types can store empty strings, but Oracle does not, although it retains the right to do so later. Oracle himself developed a data type VARCHAR2, which is not a standard varchar that stores null values in a database where varchar columns can store an empty string. If you want to be able to be backward compatible, Oracle recommends using VARCHAR2 instead of varchar.

When should I use char and when should I use VARCHAR2?

Char and VARCHAR2 are a pair of contradictory unity, the two are complementary relations. VARCHAR2 saves more space than Char, and is slightly less efficient than char, which means that if you want to be efficient, you have to sacrifice a certain amount of space, which is what we often say in database design, ' space for efficiency '. VARCHAR2 saves space than Char, but if a VARCHAR2 column is often modified and the length of each modified data varies, this causes a ' row migration ' (row migration) phenomenon, which creates redundant I/O, which is avoided in database design and tuning. , it would be better to use char instead of VARCHAR2 in this case.

A comparison of Char and VARCHAR2

The char type is compared with the char or Word constants, using padded spaces for comparison.

VARCHAR2 type and VARCHAR2 type, char type and Word constants quantity comparison, compare without adding space, direct comparison.

CREATE TABLE TT (A1 CHAR (2), A2 VARCHAR2 (2));

INSERT into TT VALUES (' A ', ' a ');

INSERT into TT values (' A ', ' a ');

COMMIT;

Comparison of--char type and character constants quantity, character constants quantity as Char type processing

--compared to ' a ', returns 2 rows, that is, when the constant ' a ' is automatically padded with space after comparison

SELECT * from TT where a1= ' A ';

A1 A2

-- --

A A

A A

--compared to ' a ', returns 2 rows, that is, when the constant ' a ' is automatically padded with space after comparison

SELECT * from TT where a1= ' A ';

A1 A2

-- --

A A

A A

Comparison of--varchar2 and constants, the constants quantity as a VARCHAR2-type processing

--Compare with ' a ', return 1 rows, that is, when comparing to ' a ' does not do processing, direct comparison

SELECT * from TT WHERE a2= ' A ';

A1 A2

-- --

A A

--Compare with ' a ', return 1 rows, that is, when comparing to ' a ' does not do processing, direct comparison

SELECT * from TT WHERE a2= ' A ';

A1 A2

-- --

A A

--When comparing a char type with a VARCHAR2 type, the value of the field is not treated as a direct comparison

--let A1 and A2 directly compare, at this time is a direct comparison, there is a record of A1 and A2 the same

SELECT * from TT where A1=A2;

A1 A2

-- --

A A

But when used in conjunction with the Decode function, there are different situations

Using the A1 field

Select Decode (A1, ' A ', ' AAAA ', ' bbbb ') from TT;

DECODE (A1, ' A ', ' AAAA ', ' bbbb ')

----------------------------

bbbb

bbbb

Although the A1 field is char (2), the comparison does not compare the constant ' A ' fill space with the field A1, but instead compares it directly, which means that two comparison fields are treated according to the VARCHAR2 type

As a result, the field A1 is not equal to the constant ' A ', and a record of two results is ' BBB ' is found.

Further verification,

Select Decode (A1, ' A ', ' AAAA ', ' bbbb ') from TT; --Here are two spaces

DECODE (A1, ' A ', ' AAAA ', ' bbbb ')

----------------------------

bbbb

bbbb

or return two ' BBB ' records to show that comparisons are not handled according to the CHAR-type comparison rules.

Using the A2 field

Select decode (A2, ' A ', ' AAAA ', ' bbbb ') from TT;

DECODE (A2, ' A ', ' AAAA ', ' bbbb ')

----------------------------

Aaaa

bbbb

This is the normal comparison between the VARCHAR2 types, the first record of the A2 field equals ' A ', returns ' AAA ', the second line records the A2 field is ' A ', the comparison is not equal, returns ' BBB '

When the case expression is used to process the A1 field, there is a different processing result than the Decode function

Using Fields A1

Select Case A1 when ' A ' then ' AAA ' Else ' BBB ' end from TT;

Casea1when ' A ' THEN ' AAA ' ELSE ' BBB

------------------------------

Aaa

Aaa

When using the A1 field in a case statement to compare to constant ' A ', the two comparison values are processed according to the comparison rule of char type, compared after the right complement space, so that two records are returned

Further verification, as follows,

Select Case A1 when ' A ' then ' AAA ' Else ' BBB ' end from TT;

Casea1when ' A ' THEN ' AAA ' ELSE ' BBB

------------------------------

Aaa

Aaa

The constant is changed to ' A ', compared to the char type, and the field A1 to the constant after the space is padded.

Using the A2 field

Select Case A2 when ' A ' then ' AAA ' Else ' BBB ' end from TT;

Casea2when ' A ' THEN ' AAA ' ELSE ' BBB

------------------------------

Aaa

Bbb

This is the normal comparison between the VARCHAR2 types, the first record of the A2 field equals ' A ', returns ' AAA ', the second line records the A2 field is ' A ', the comparison is not equal, returns ' BBB '

Summarize

When comparing a char field using the Decode function, it is necessary to note that even if the two fields of comparison are char types, the Decode function converts it to a varchar2 type and does not follow a char-type comparison rule

For char data, in the set operation, the actual data of char is compared, not according to the comparison rules for char data.

Here is a simple example

CREATE table T1 (name char (10));

CREATE TABLE T2 (name char (20));

Begin

For I in 1..5 loop

INSERT into T1 values (To_char (i));

INSERT into T1 values (To_char (i));

End Loop;

Commit

End;

Select name from t1 minus select name from T2;

NAME

--------------------

1

2

3

4

5

If you follow a char-type comparison rule, you should not have a return value.

Select anem from t1 intersect select name from T2;

NAME

--------------------

No return value, and it also indicates that the comparison of the collection operation was not followed by a char-type comparison rule.

=============================== Review 2============================

The difference between decode and case for char types is due to the fact that it is an expression and that decode is a function.

The input parameters and return values in a function are generally defined as VARCHAR2 types.

Thanks for pointing, I didn't think of it here, look at the definition of the Decode function, as you mentioned, type conversion of char data using the Decode function.

The following is a parameter definition for the DECODE function.

Sql> desc Sys.standard.decode

Parameter Type Mode Default?

--------- ----------------------- ---- --------

(Result) Number

EXPR number in

PAT number in

RES number in

(Result) VARCHAR2

EXPR number in

PAT number in

RES VARCHAR2 in

(Result) DATE

EXPR number in

PAT number in

RES DATE in

(Result) Number

EXPR VARCHAR2 in

PAT VARCHAR2 in

RES number in

(Result) VARCHAR2

EXPR VARCHAR2 in

PAT VARCHAR2 in

RES VARCHAR2 in

(Result) DATE

EXPR VARCHAR2 in

PAT VARCHAR2 in

RES DATE in

(Result) Number

EXPR DATE in

PAT DATE in

RES number in

(Result) VARCHAR2

EXPR DATE in

PAT DATE in

RES VARCHAR2 in

(Result) DATE

EXPR DATE in

PAT DATE in

RES DATE in

(Result) SYS. STANDARD.

EXPR SYS. STANDARD. In

PAT SYS. STANDARD. In

RES SYS. STANDARD. In

(Result) SYS. STANDARD.

EXPR SYS. STANDARD. In

PAT SYS. STANDARD. In

RES SYS. STANDARD. In