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;