How many bytes of a character in Oracle?

22.1 Problem Description

Maybe you can say that a Chinese character occupies 2 bytes, Which is certain? How to calculate the number of bytes of a string?

22.2 Solutions

In Oracle, a character, especially Chinese, occupies several bytes.

For example, if I create a table create table test_ly (A varchar2 (4), B nvarchar2 (4 ))

Can you insert two Chinese characters into column?

Error! You can insert at most one Chinese character with one letter (or number ).

Why? Because a Chinese character occupies three bytes in field a, and other characters (such as numbers, letters, or punctuation marks) occupy one byte,

How many Chinese characters can be inserted at most in column B?

Error! Column B can contain up to four Chinese characters. A Chinese character or other characters (such as numbers, letters, or punctuation marks) in column B are two bytes.

 

It can be seen that in the Field Types starting with N (such as nchar and nvarchar2), any one character (including one Chinese character) occupies 2 bytes and is unified.

In fields not starting with N (such as char and varchar2), Unicode characters (such as Chinese characters) occupy three bytes, while other characters occupy one byte.

 

How to calculate the number of characters and the number of characters occupied by a string?

The length function obtains the number of characters in use, and the lengthb or vsize function obtains the number of bytes in use.

How many characters and bytes are occupied by the Chinese character string 12? The SQL return value is clear.

Select length ('zhonghua 12') from dual -- returns 4, which occupies 4 characters

Select lengthb ('zhonghua 12') from dual -- returns 8, which occupies 8 bytes. The Chinese son occupies 3 bytes, and the 12 character occupies one byte.

Select lengthb (n'zhonghua 1') from dual -- returns 6, which is to convert the string into a unicode string, each character occupies 2 bytes, 3 is 6 bytes

Select length (N 'China') from dual -- returns 3 because it only contains three characters.

 

The default length of a string field is byte, depending on the value of the nls_length_semantics parameter. The default value is byte, it is measured in bytes. If it is Char, it is measured in characters. Can a column in a table use characters? Of course, you can create it like this: Create Table test_ly (A varchar2 (4 char) so that column A can store up to four characters instead of four bytes.

 

 

 

Http://luoyong0201.blog.163.com/blog/static/1129305201022110221897/