[ORACLE] processing of oracle string objects, oracle string objects

[ORACLE] processing of oracle string objects, oracle string objects

2.1 char field, varchar2 Field

(1) Defining the field length char (10) indicates that the field can contain 10 English characters (regardless of the encoding method, do English characters occupy 10 bytes ?), The physical space is always allocated with 10 characters. The variable length varchar (10) indicates that up to 10 English characters can be stored, and the physical space is allocated. The actual space is allocated based on the actual field data.

(2) The longest field type length of char and varchar2

Char (2000), varchar2 (4000)

(3) question about the varchar field in Chinese.

The field is defined as varchar (2). You cannot insert the Chinese character "hello. Because the space occupied by "hello" exceeds the maximum space of two English characters

A single English character occupies several bytes and a Chinese character occupies several bytes. The character encoding rules are required.

The encoding function is as follows:

Select userenv ('language') from dual;

When designing a table, the varchar2 field capacity is generally designed based on three times the number of Chinese characters.

(4) nvarchar type

The Nvarchar2 type solves the problem of varchar2 in Chinese. nvarchar (2) can only store up to two characters regardless of Chinese or English characters.

2.2-character processing functions

(1) trim, ltrim, and rtrim remove spaces from the string header or tail.

(2) lpad: The rpad function defaults to a space and fills the space before or after a certain length.

For example,

Select lpad (lname, 50, '*') from foo_6;

Select lpad (lname, 50) from foo_6;

(1) Use the substr () function to obtain all characters after the nth character (the character subscript in oracle starts from 1)

Substr (lname, 3 );

You can define the length of the obtained characters as 5,

For example

Substr (lname, 3, 5)

(2) substr start position subscript, which can be a negative number, indicating the position of the forward number from the end, take the string or return.

For example,

Substr (lname,-5) indicates the last five characters.

(3) The instr function finds the subscript position of a character.

Insrt (lname, 'Find characters', start position)

You can find the location of the first occurrence,

For example

Insr (lname, 'view characters', start position, number of occurrences)

(4) The length function obtains the number of characters.

Length ("hello") length ('AB') returns 2 characters, indicating two characters. The maximum subscript is 2, not the number of bytes occupied.

3 oracle numeric objects

Number (10) valid Number is 10

Number () valid Number is 10, containing 2 decimal places