Common Oracle numeric functions, conversion functions, and string functions

Numeric functions:

ABS (m) Absolute Value of 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]) The return value is St, And st is to delete 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]) The return value is St, And st is to delete 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 at the nth occurrence of st2. 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 number 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 repeated 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) Date of the last day of the month that contains d
Month_between (d, e) number of months between D and E, and E is prior to d
New_time (D, a, B) The date and time d of the time zone are the date and time of the B time zone.
Next_day (D, Day) The date of the week specified by day later than the date d
Sysdate Current System Date and Time
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 is rounded 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   Description  Example or value range
--------------------------------
Dd           One day of the month 1-3
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
Rm      Roman numerals of the month I,... XII
YY or yyyy     Two-digit, 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.
FM           The prefix is not allowed to be filled with monthly, daily, or yearly values.
---------------------------------
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;