Oracle Common Feature Collection

numeric function:

Absolute value of ABS (m) m

MoD (M,n) m is the remainder after the n is removed

Power (M,n) m's n-th-side

Round (M[,n]) m rounded to n digits after the decimal point (n defaults to 0)

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

Character functions:

Initcap (ST) return St to capitalize the first letter of each word, all other letters lowercase

Lower (ST) return St will each word the letter all lowercase

Upper (ST) return St to capitalize all the letters of each word

Concat (ST1,ST2) returns to the end of the St St2 St1 (available operator "| |")

Lpad (St1,n[,st2]) returns the st,st to the left of the st1 with a st2 padding until the length is N,st2

Rpad (St1,n[,st2]) returns a left-aligned st,st that is populated with st2 to the right of the st1 until the length is n,st2 by default of a space

LTrim (St[,set]) returns st,st the character that deletes the set character from the left until the first one is not set. By default, it refers to a space

RTrim (St[,set]) returns a character that St,st deletes a set character from the right until the first one is not set. By default, it refers to a space

Replace (St,search_st[,replace_st]) replaces each search_st that appears in St with Replace_st, returning a St. By default, delete Search_st

SUBSTR (St,m[,n]) n= returns the substring of the St string, starting with the M position and taking n characters long. By default, always return to the St end

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

InStr (St1,st2[,m[,n]]) value, which returns the position where the st1 occurs from the first m character, st2 the nth occurrence, and the default value of M and N is 1

Cases:

1.

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

INITCA INITCA

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

Thomas Thomas

2.

Select Concat (' abc ', ' Def ') ' "a" from test;

The

-----

ABCdef

3.

Select ' ABC ' | | '||' Def ' "a" from test;

The

-----

ABC def

4.

Select Lpad (name,10), Rpad (name,5, ' * ') from test;

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

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

MMX mmx**

ABCdef ABCDE

5.

Remove points and words from the end of the Address field St and Rd

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 (' Archibald Bearisol ', 6,9) a,substr (' Archibaldbearisol ', one) b from test;

Ab

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

Bald Bear Bearisol

8.

Select NAME,INSTR (Name, ') a,instr (name, ', 1,2) b fromtest;

Nameab

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

Li Lei30

L I L24

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

Conversion function:

NVL (M,n) if the M value is null, returns N, otherwise returns m

To_char (M[,fmt]) m is converted from a numeric value to a string of the specified format FMT default, the width of the FMT value is just enough to hold all the valid digits

To_number (St[,fmt]) St converts from character data to numeric values in the specified format, and the numeric format string is the exact size of the entire number by default

Report:

Format of the To_char () function:

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

Symbol description

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

9 Each 9 represents a number in the result

0 represents the pilot 0 to display

$ dollar sign printed on the left of the number

L arbitrary local currency symbol

. print decimal Point decimal

, print the comma that represents the decimal point

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

Cases:

1.

Select To_number (' 123.45 ') +to_number (' 234.56 ') Form test;

To_number (' 123.45 ') +to_number (' 234.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

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;

Abc

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

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

4.

Select To_char (1234.1234, ' 999,999.999 ') A,to_char (0.4567, ' 999,999.999 ') B,to_char (1.1, ' 999,999.999 ') fromtest;

Abc

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

1,234.123.4571.100

Grouping functions:

Average of AVG ([Distinct/all] n) column n

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

Count ([Distinct/all] n) The number of rows with non-null values

Max ([Distinct/all] n) The maximum value of the column or expression

MIN ([Distinct/all] n) The minimum value of the column or expression

Stdev ([distinct/all]n) the standard deviation of the column or expression, ignoring the null value

SUM ([Distinct/all] n) The total of the column or expression

Variance ([Distinct/all]n) The variance of the column or expression, ignoring the null value

Date function:

>

Add_months (d,n) Date d plus n months

Last_day (d) Date of the last day of the month that contains D

Month_between (D,e) The number of months between D and E, E before D

New_time (d,a,b) a time zone date and time d the date and time in the B time zone

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

Sysdate the current system date and time

Greatest (D1,d2,... dn) The last date in the list of dates given

Least (D1,K2,... dn) The earliest date in the list of dates given

To_char (D[,FMT]) Date D is converted to a string in the format specified by FMT

To_date (St[,fmt]) string St in fmt specified format to date value, if FMT ignored, St to use the default format

Round (D[,FMT]) Date D is rounded to the nearest date by FMT specified format

Trunc (D[,FMT]) Date d is truncated to the most recent date by FMT specified format

Report:

Date format:

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

A range of examples or desirable values for a format code description

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

DD One day of the month 1-3

DY three uppercase letters of the week sun, ... Sat

Day complete week, uppercase English Sunday, ... SATURDAY

MM Month 1-12

MON three uppercase letters of the month, ... DEC

Month Complete January,... December

Roman numeral I,... of the RM month XII

yy or yyyy two-bit, four digit years

HH:MI:SS: minutes: Seconds

HH12 or HH24 display in 12 hours or 24 hours

MI min

SS seconds

Am or PM on PM indicator

SP suffix SP requires spelling out any numeric fields

th suffix th indicates that the added number is ordinal 4th,1st

FM prefix to month or day or year value, no padding