Highlights of common Oracle Functions

Numeric functions:

Absolute Value of ABS (m) m

MoD (m, n) m remainder after N Division

Power (m, n) M's n power

Round (M [, N]) the value of M rounded to N digits after the decimal point (N defaults to 0)

Trunc (M [, N]) m truncates the value of N decimal places (N defaults to 0)

--------------------------------------------------------------------------------

Character functions:

Initcap (ST) returns the first letter of each word in the St case, and all other letters in the lower case

Lower (ST) returns st to lowercase letters of each word

Upper (ST) returns st to uppercase for each word

Concat (ST1, st2) returns the end of st2 connected to ST1 (available operator "| ")

Lpad (ST1, N [, st2]) returns the right-aligned st. St is filled with st2 on the left of ST1 until the length is n. The default space of st2 is

Rpad (ST1, N [, st2]) returns the left-aligned st. St is filled with st2 on the Right of ST1 until the length is n. The default space of st2 is

Ltrim (ST [, set]) returns St, where St deletes the characters in the set from the left until the first character is not in the set. Lack of time, refers to Space

Rtrim (ST [, set]) returns St, where St deletes the characters in the set from the right until the first character is not in the set. Lack of time, refers to Space

Replace (St, search_st [, replace_st]) replaces the search_st that appears in the St with replace_st, and returns a st. Delete search_st

Substr (St, M [, N]) n = returns the substring of the St string, starting from the M position and taking n characters in length. Lack of time, always return to the end of St

Length (ST) value, returns the number of characters in St.

Instr (ST1, st2 [, M [, N]), returns the position where ST1 appears for the nth time starting from the nth character. The default values of M and N are 1.

Example:

1.

Select initcap ('Thomas '), initcap ('Thomas') from test;

Initca

------------

Thomas

2.

Select Concat ('abc', 'def ') "first" from test;

First

-----

Abcdef

3.

Select 'abc' | ''| 'def '" first "from test;

First

-----

ABC def

4.

Select lpad (name, 10), rpad (name, 5, '*') from test;

Lpad (name, 10) rpad (name, 5 ,'*')

----------------------------

MMX **

Abcdef ABCDE

5.

Remove the vertices and words st and RD at the end of the address field.

Select rtrim (address, '. St RD') from test

6.

Select name, replace (name, 'A', '*') from test;

Name Replace (name, 'A ','*')

-------------------------

Great GRE * t

7.

Select substr ('archibbard bearisol ', 6, 9) A, substr ('archibbard bearisol', 11) B from test;

A B

--------------

Bald Bear bearisol

8.

Select name, instr (name, '') A, instr (name,'', 1, 2) B from test;

Name a B

------------------------

Li Lei 3 0

L I L 2 4

%

Conversion functions:

Nvl (m, n) If M is null, n is returned; otherwise, M is returned.

To_char (M [, FMT]) m is converted from a numeric value to a string in the specified format. FMT is short of time, and the width of the FMT value can accommodate all valid numbers.

To_number (ST [, FMT]) ST is converted from dense data to numeric values in the specified format. The size of the missing time-saving numeric string is exactly the whole number.

Appendix:

Format of to_char () function:

---------------------------------

Symbol description

---------------------------------

9 each 9 represents a digit in the result.

0 indicates the pilot 0 to be displayed

$ Dollar sign printed on the left of the number

L any local currency symbol

. Print decimal point

, Print the comma representing the kilobytes

---------------------------------

Example:

1.

Select to_number ('192. 45') + to_number ('192. 56') form test;

To_number ('192. 45') + to_number ('192. 56 ')

----------------------------------------

358.01

2.

Select to_char (987654321) from test;

To_char (987654321)

------------------

987654321

3.

Select to_char (123, '$9,999,999') A, to_char (54321, '$9,999,999') B, to_char (9874321, '$9,999,999') C from test;

A B C

----------------------------

$123 $54,321 $9,874,321

4.

Select to_char (1234.1234, '000000') A, to_char (999,999.999, '000000') B, to_char (0.4567, '000000') from test;

A B C

-------------------------------

1,234.123. 457 1.100

--------------------------------------------------------------------------------

Grouping functions:

AVG ([distinct/All] n) column N average

Count ([all] *) returns the number of rows in the query range, including duplicate values and null values.

Count ([distinct/All] n) Number of non-null rows

Max ([distinct/All] n) maximum value of this column or expression

Min ([distinct/All] n) minimum value of the column or expression

STDev ([distinct/All] n) Standard deviation of this column or expression, ignoring null values

Sum ([distinct/All] n) sum of the column or expression

Variance ([distinct/All] n) variance of the column or expression, ignoring null values

--------------------------------------------------------------------------------

Date functions:

>

Add_months (d, n) date D plus N months

Last_day (d) contains the date of the last day of the month of d

Month_between (d, e) number of months between D and E, and E is prior to d

New_time (D, a, B) Date and time of a Time Zone D date and time of B Time Zone

Next_day (D, day) is later than date D, which is the date of the week specified by day

Current system date and time of sysdate

The last date in the date list given by greatest (D1, D2,... DN)

The earliest date in the date list given by least (D1, K2,... DN)

To_char (d [, FMT]) date D is converted into a string in the format specified by FMT

To_date (ST [, FMT]) string st is converted to a date value in the format specified by FMT. If FMT is ignored, St must use the default format.

Round (d [, FMT]) date D rounds to the latest date in the format specified by FMT

Trunc (d [, FMT]) date D is truncated to the latest date in the format specified by FMT

Appendix:

Date Format:

--------------------------------

Format code example or value range

--------------------------------

DD: 1-3 in a certain day of the month

The three uppercase letters of Dy indicate the day of the week sun,... Sat

The complete day of the week, in uppercase Sunday,... Saturday

Mm Month 1-12

Month Jan,... Dec

Month complete January,... December

The Roman numerals I,... XII of RM month

YY or yyyy, four-digit year

Hh: MI: SS hour: minute: Second

Hh12 or hh24 is displayed in 12 hours or 24 hours

Mi score

SS seconds

AM or PM afternoon indicator

SP suffix SP requires spelling out any numeric field

The th suffix indicates that the number to be added is 4 th, 1 st.

Fill in is prohibited for month, day, or year with the FM prefix.

---------------------------------

Example:

1.

Next Friday's date

Select next_day (sysdate, 6) from test;

2.

Today's date two months ago

Select add_months (sysdate,-2) from test;