Oracle Data Types and storage methods

Welcome to the Oracle community forum and interact with 2 million technical staff. When we insert 10 Chinese characters in the col_char column, the length of the 10 Chinese characters is 20. although we use varchar2 (10, char) in the definition ). it can be seen that oracle determines this field based on the character set used by the current database, the number of bytes occupied by each character X Field Length

Welcome to the Oracle community forum and interact with 2 million technical staff> when we insert 10 Chinese characters in the col_char column, its length is 20. although we use varchar2 (10, char) in the definition ). it can be seen that oracle determines this field based on the character set used by the current database, the number of bytes occupied by each character X Field Length

Welcome to the Oracle community forum and interact with 2 million technical staff> enter

When we insert 10 Chinese characters in the col_char column, its length is 20.

Although we use varchar2 (10, char) in the definition ).

Therefore, oracle determines the number of bytes occupied by this field based on the character set used by the database.

In this example, varchar2 (10, char) is equivalent to varchar2 (20 ).

Don't believe it. Let's try it.

SQL> desc test_varchar2;

Name Type Nullable Default Comments

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

COL_CHAR VARCHAR2 (20) Y

COL_BYTE VARCHAR2 (10) Y

When multi-byte character sets are used, it is better to specify the Field Length in units of char. This prevents the length of a field.

You can use the lengthb function when you do not know how many bytes a character occupies in the current database.

SQL> select lengthb ('yuan ') from dual;

LENGTHB ('yuan ')

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

2

§ 1. 4 char or varchar

1. Create a new table. Column 1 is char type and column 1 is varchar2 type.

SQL> create table test_char_varchar (char_col char (20), varchar_col varchar2 (20 ));

Table created

2. Insert related data into both columns of the table

SQL> insert into test_char_varchar values ('Hello world', 'Hello World ');

1 row inserted

SQL> select * from test_char_varchar;

CHAR_COL VARCHAR_COL

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

Hello World

3. query by char_col as a condition

SQL> select * from test_char_varchar where char_col = 'Hello world ';

CHAR_COL VARCHAR_COL

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

Hello World

4. Use the varchar_col column as a condition Query

SQL> select * from test_char_varchar where varchar_col = 'Hello world ';

CHAR_COL VARCHAR_COL

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

Hello World

5. It seems that the char and varchar types are no different. Let's look at the following statement.

SQL> select * from test_char_varchar where varchar_col = char_col;

CHAR_COL VARCHAR_COL

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

This shows that they are not the same, which involves the problem of string comparison.

Because implicit conversion has occurred, the content of the char_col column has been converted to char (20) when compared with char_col of the char column. After Hello World, it is filled with spaces. The varchar_col column does not undergo this conversion.

If you want to make the char_col column equal to the varchar_col column. There are two methods.

First, use trim to remove spaces in the char_col column.

The second type is: enable remote rpad to fill the varchar_col column with spaces with 20 characters in length.

SQL> select * from test_char_varchar where trim (char_col) = varchar_col;

CHAR_COL VARCHAR_COL

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

Hello World

SQL> select * from test_char_varchar where char_col = rpad (varchar_col, 20 );

CHAR_COL VARCHAR_COL

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

Hello World

If you use the trim function, if the char_col column has an index, the index will be unavailable.

In addition, problems may occur when variables are bound.

[1] [2] [3]