Summary of basic Oracle Functions

(1) character Functions

Upper

Select upper ('abcde') from dual result: ABCDE

 

Lower

Select lower ('abcde') from dual result: ABCDE

 

Initcap

Select initcap ('aaa') from dual result: AAA

Select initcap ('aaa') from dual result: AAA

 

Concat

Select Concat ('A', 'B') from dual result: AB

Select 'A' | 'B' from dual result: AB

 

Substr

Select substr ('abcde', 0, 3) from dual result:ABC

 

Select substr ('abcde',-2, 3) from dual result: de

The second parameter is the nth digit of the string. The default value starts from 0. When it is a negative number, it starts from the right side. The third parameter is the total length of the string to be obtained.

 

Length

Select length ('abcde') from dual result: 5

 

Replace

Select Replace ('abcde', 'A', 'A') from dual result: ABCDE

 

Instr

Select instr ('Hello world', 'w') from dual result: 8

Select instr ('Hello world', 'w') from dual result: 0

If the second parameter exists in the first parameter, the location of the First Matching parameter is returned. This method is case sensitive.

 

Lpad

Select lpad ('Smith ', 10,' * ') from dual result: ***** Smith

 

Rpad

Select rpad ('Smith ', 10,' * ') from dual result: Smith *****

 

Trim

Select trim ('Mr Smith ') from dual result: Mr Smith

 

(2) numeric Functions

Round

Select round (412,-2) from dual; Result: 400

Select round (412.713) from dual; Result: 413

Select round (412) from dual; Result:

Select round (412.713, 2) from dual; Result: 412.71

The second parameter specifies the number of digits after the decimal point. If the number is 5, the second parameter is used.

 

MoD

Select Mod (198,2) from dual result: 0

 

Trunc

Select trunc (412.13,-2) from dual result: 400

Select trunc (412.13, 2) from dual result: 412.13

Select trunc (412.13, 1) from dual result: 412.1

Select trunc (412.53) from dual result: 412

The second parameter specifies the digits after the decimal point, without carrying.

 

(3) date functions

Months_between

Select months_between (sysdate + 35, sysdate) from dual

Result: 1.16129032258065

Use the time of the first parameter minus the time of the second parameter to get the difference of the month.

 

Add_months

Select add_months (sysdate, 1) from dual

Adds a month to the current month.

 

Interval

Select sysdate + interval '3' year from dual

The current date is added for three years.

Select sysdate + interval '3-2' year to month from dual

The current date is added to three years and two months.

Select sysdate + interval '10:10' day to minute from dual

The current date is plus ten days, two hours, and ten minutes.

 

Next_day

Select next_day (sysdate, 'monday') from dual

The date of the next Monday is selected.

 

Last_day

Select last_day (sysdate) from dual

Select the last day of the month

 

(4) conversion functions

To_char

Select to_char (sysdate, 'yyyy-mm-dd') from dual; Result: 2010-10-30

 

To_number

Select to_number ('13') + to_number ('14') from dual; Result: 27

 

To_date

Select to_date ('1970-1-1 ', 'yyyy-mm-dd') from dual; Result: 2012-1-1

 

(5) General Functions

Nvl

Select nvl ('string', 0) from dual result: String

Select nvl ('', 0) from dual result: 0

 

Nullif

Select nullif ('abc', 'abc') from dual result: NULL

Select nullif ('abc', 'abc') from dual result: 'abc'

 

Nvl2

Select nvl2 ('A', 'B', 'C') from dual result: B

Select nvl2 ('', 'B', 'C') from dual result: c

If the first parameter is not null, the second parameter is returned. Otherwise, the third parameter is returned.

 

Coalesce

Select coalesce ('','', 'abc', '') from dual result: ABC

Select coalesce ('','') from dual result: NULL

The parameters are checked in sequence. If there is a non-null parameter, the parameter is stopped and the value is returned.

 

Case expression

Select case 4

When 10 then

'Finance author'

When 20 then

'R & D departments'

When 30 then

'Sales directory'

Else

'Unknown departments'

End Department

From dual

Result: Unknown department.

 

Decode expression

Select decode (10, 10, 'Finance Department ', 20, 'r & D department', 30, 'sales Department ', 'unknown departments') Department

From dual; Result: Finance Department

 

Select decode (, 'Finance authorization', decode (, 'r & D authorization', decode (, 'sales authorization', 'unknown authorization') from dual; Result: Unknown Department

Whether the first parameter is equal to the second parameter. If it is equal to the second parameter, the third parameter is returned. Otherwise, the fourth parameter is returned and can be used for Row-to-column conversion.