Introduction to Oracle Common numerical functions, conversion functions, String functions

oracle| Function | conversion | String Oracle Common numerical functions, conversion functions, string functions introduced.
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, deletes the 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; ABCDEF-----
3. Select ' ABC ' | | '||' Def ' "a" from test; -----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 (addressing, '. 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 (' Archibald Bearisol ', one) 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 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 accommodate all valid digits To_number (ST[,FMT]) St converts from character data to a value in the specified format. By default, the numeric format string is exactly the same size as the whole number: the format of the To_char () function:---------------------------------symbol description---------------------------------9 Each 9 represents a number in the result of 0 representing the leading 0 $ dollar symbol to be displayed printed on the left L arbitrary local currency symbol for the number. Print decimal decimal points, print a comma that represents the thousand---------------------------------example:
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 test; A b c----------------------------$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 ') from test; A b c-------------------------------1,234.123.457 1.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 nearest date by FMT specified format:
Date format:--------------------------------
Format code
Description
A range of examples or desirable values--------------------------------
DD one day of the month 1-3 DY
Three capital letters of the week SUN, ... SAT Day full of weeks, uppercase English SUNDAY, ... SATURDAY MM Month 1-12 MON three
The month of a capital letter, ... DEC MONTH Complete January,... December RM Month Roman numeral I,... XII yy or yyyy two-bit, four-digit year HH:MI:SS: minutes: sec HH12 or HH24 with 12 hours or 24 hours to show MI minute SS sec am or PM last afternoon 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---------------------------------case:
1. Next Friday Date Select Next_day (sysdate,6) from test; 2. Two months ago today's Date Select Add_months (sysdate,-2) from test;