How many bytes does VARCHAR2 occupy? NLS_LENGTH_SEMANTICS, nls_language, nlslengthsemantics

How many bytes does VARCHAR2 occupy? NLS_LENGTH_SEMANTICS, nls_language, nlslengthsemantics
ORACLE initialization parameter: NLS_LENGTH_SEMANTICS

The initialization parameter NLS_LENGTH_SEMANTICS is used to specify the length definition method for the CHAR or VARCHAR2 columns. The default value is BYTE.

When this parameter is set to BYTE, the CHAR or VARCHAR2 columns are defined in the BYTE length mode;

When this parameter is set to CHAR, the number of characters is used for defining the CHAR or VARCHAR2 columns.

Note that setting this parameter does not apply to existing parameters.

Production database

Nls_language string SIMPLIFIED CHINESE

Nls_length_semantics string BYTE

Test Library

Nls_language string AMERICAN

Nls_length_semantics string BYTE

Is varchar2 (20) the same as varchar2 (20 byte?

The difference is determined by the database parameter NLS_LENGTH_SEMANTICS, which has two units: char (character) or byte. The default value of this parameter is BYTE. Therefore, by default, varchar2 (20) = varchar2 (20 bytes ). If the parameter value is CHAR, It is not equal.

Two databases and one table

Select PERSON

From temp_opt_order_trace

Where LENGTH (PERSON) = 21

Personvarchar2 (32)

'The second Lang Bus Station opposite the 18355200098 sign'

In this case, VARCHAR2 (32) is a character that is not affected by NLS_LENGTH_SEMANTICS's BYTES or by the nls_language language. In my 11.2.0.0.1 library.

In fact, the affected PERSON has a maximum of 21 characters. This is the character length, that is, the number of characters, letters, Chinese characters, and numbers. in fact, the corresponding BYTES occupies 2 Chinese characters, and letters and numbers occupy 1.

Nls_language if it is UNICODE UTF-8 AU32 Chinese is accounted for 3 bytes.