Processing of "Oracle" Oracle String objects

2.1 char field , varchar2 field

(1) define the field length char (x) indicates that the field can hold up to ten characters in English (regardless of the encoding method, does the English character account for ten bytes?). ), and when allocating physical space, always allocate a position of ten characters. Defined variable long varchar (TEN) indicates that a maximum of ten English characters can be stored, and the physical space allocated is, according to the actual field of data, allocate the actual space

(2) length of the longest field type for char and varchar2

Char (+), VARCHAR2 (4000)

(3) The Chinese question of the varchar field.

The definition field is varchar (2), and inserting Chinese characters " Hello " is not plugged in. Because " Hello " occupies more space than the maximum space of two English character space

An English character occupies a few bytes, and a Chinese character takes a few bytes to see the encoding rules of the characters.

The functions for viewing the encoding are:

Select Userenv (' language ') from dual;

When designing a table, you typically design the capacity of the varchar2 field by three times times the number of Chinese characters.

(4) nvarchar type

The Nvarchar2 type solves the Chinese problem of the varchar2 type,nvarchar (2), which can hold up to two characters, regardless of the character or English characters.

Processing functions for 2.2 characters

(1) Trim,ltrim,rtrim remove whitespace from string header or tail

(2) Lpad,rpad function By default according to space, before or after filling to 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 the characters following the nth character (theoracle Poute is calculated from 1 )

Substr (lname,3);

You can define the length of the get character 5,

For example

Substr (lname,3,5)

(2) substr The starting position subscript, can be negative, indicating the position from the back to the next number, take a string or take a backward.

For example

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

(3) instr function to find the subscript position of the character

insrt (lname, ' find character ', start position )

You can find the location of the first occurrence,

For example

INSR (lname, ' view character ', starting position, first occurrence )

(4) length function Gets the number of characters

Length (" Hello ") Length (' ab ') return value is 2, representing two characters, character subscript maximum is 2, does not represent the number of bytes occupied.

3 Oracle Digital objects

Number (Ten) a valid digit is ten

Number (10,2) is a valid digit, with 2 decimal places

Processing of "Oracle" Oracle String objects