Numerical function:
Abs(m) m absolute value
mod(m,n) remainder after m is divided by n
power(m,n) m to the n power
round(m[,n]) m rounded to the value of n bits after the decimal point (n is 0 by default)
trunc(m[,n]) m truncates the value of the n-digit decimal places (n is 0 by default)
--------------------------------------------------------------------------------
Character functions:
initcap(st) returns st capitalizes the first letter of each word and lowercases all other letters
lower(st) Returns st to lowercase the letters of each word
upper(st) Return to st Convert all letters of each word to capitalize
concat(st1,st2) Returns st as st2 and the end of st1 (the operator "||" can be used)
lpad(st1,n[,st2]) returns the right-aligned st. St is filled with st2 on the left side of st1 until the length is n. The default space for st2 is
rpad(st1,n[,st2]) returns the left-aligned st. St is filled with st2 on the right side of st1 until the length is n. The default space for st2 is
ltrim(st[,set]) returns st, st is to delete the character in set from the left until the first character is not the character in set. By default, it refers to spaces
rtrim(st[,set]) returns st, st is to delete the character in set from the right until the first character is not the character in set. By default, it refers to spaces
replace(st,search_st[,replace_st]) replaces each search_st that appears in st with replace_st, and returns a st. By default, delete search_st
substr(st,m[,n]) n=Returns the substring of the st string, starting from the m position, taking n characters in length. By default, it returns to the end of the st
length(st) value, return the number of characters in st
instr(st1,st2[,m[,n]]) value, return the position where st1 starts from the mth character and st2 appears nth the nth time. The default value of m and n is 1
example:
1.
select initcap('THOMAS'),initcap('thomas') from test;
initca initca
------ ------
Thomas 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 mmx**
abcdef abcde
5.
Remove the points 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('archibald bearisol',6,9) a,substr('archibald 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 function:
nvl(m,n) If m value is null, return n, otherwise return m
to_char(m[,fmt]) m When converting a numeric value to a string of fmt in a specified format by default, the width of the fmt value can just accommodate all valid numbers
to_number(st[,fmt]) st converts from character data to numeric values in the specified format. By default, the size of the numeric format string is exactly the entire number
Attached:
Format of to_char() function:
---------------------------------
Symbol Description
---------------------------------
9 Each 9 represents a digit in the result
0 represents the pilot to be displayed 0
The $ dollar sign is printed on the left side of the number
L Any local currency symbol
. Print decimal points in decimal
, print the comma representing the thousandths
---------------------------------
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:
avg([distinct/all] n) average value of column n
count([all] *) Returns the number of rows in the query range including duplicate values and null values
count([distinct/all] n) Number of rows with non-null values
max([distinct/all] n) The maximum value of this column or expression
min([distinct/all] n) The minimum value of this column or expression
stdev([distinct/all] n) Standard deviation of this column or expression, null values are ignored
sum([distinct/all] n) The sum of the column or expression
variance([distinct/all] n) The variance of this column or expression, null value is ignored
--------------------------------------------------------------------------------
Date function:
>
add_months(d,n) date d plus n months
last_day(d) The date of the last day of the month containing d
month_between(d,e) The number of months between dates d and e, e precedes d
new_time(d,a,b) date and time of time in time zone a and time in time zone b
next_day(d,day) is later than date d, the date of the week specified by day
sysdate Current system date and time
greatest(d1,d2,...dn) The last date in the given date list
least(d1,k2,...dn) The earliest date in the given date list
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 into date value in the format specified by fmt. If fmt is ignored, st should use the default format
round(d [,fmt]) Date d is rounded to the most recent date in the specified format of fmt
trunc(d [,fmt]) Date d is truncated to the most recent date according to the format specified by fmt
Attached:
Date format:
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
Format code Description Example or range of acceptable values
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
DD One day of the month 1-3
DY The weekly day represented by three capital letters SUN,...SAT
DAY Full weekdays, capital English SUNDAY,...SATURDAY
MM Month 1-12
MON Month represented by three capital letters JAN,...DEC
MONTH full JANUARY,...DECEMBER
RM Roman numerals for the month I,...XII
YY or YYYY Two digits, four digits of year
HH:MI:SS Hours: minutes: seconds
HH12 or HH24 is displayed in 12 hours or 24 hours
MI points
SS seconds
AM or PM PM PM indicator
SP suffix SP requires spelling any numeric field
TH suffix TH means that the added number is an ordinal number 4th, 1st
FM prefix is for month, day or year value, no filling is allowed
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
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;
Abs(m) m absolute value
mod(m,n) remainder after m is divided by n
power(m,n) m to the n power
round(m[,n]) m rounded to the value of n bits after the decimal point (n is 0 by default)
trunc(m[,n]) m truncates the value of the n-digit decimal places (n is 0 by default)
--------------------------------------------------------------------------------
Character functions:
initcap(st) returns st capitalizes the first letter of each word and lowercases all other letters
lower(st) Returns st to lowercase the letters of each word
upper(st) Return to st Convert all letters of each word to capitalize
concat(st1,st2) Returns st as st2 and the end of st1 (the operator "||" can be used)
lpad(st1,n[,st2]) returns the right-aligned st. St is filled with st2 on the left side of st1 until the length is n. The default space for st2 is
rpad(st1,n[,st2]) returns the left-aligned st. St is filled with st2 on the right side of st1 until the length is n. The default space for st2 is
ltrim(st[,set]) returns st, st is to delete the character in set from the left until the first character is not the character in set. By default, it refers to spaces
rtrim(st[,set]) returns st, st is to delete the character in set from the right until the first character is not the character in set. By default, it refers to spaces
replace(st,search_st[,replace_st]) replaces each search_st that appears in st with replace_st, and returns a st. By default, delete search_st
substr(st,m[,n]) n=Returns the substring of the st string, starting from the m position, taking n characters in length. By default, it returns to the end of the st
length(st) value, return the number of characters in st
instr(st1,st2[,m[,n]]) value, return the position where st1 starts from the mth character and st2 appears nth the nth time. The default value of m and n is 1
example:
1.
select initcap('THOMAS'),initcap('thomas') from test;
initca initca
------ ------
Thomas 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 mmx**
abcdef abcde
5.
Remove the points 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('archibald bearisol',6,9) a,substr('archibald 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 function:
nvl(m,n) If m value is null, return n, otherwise return m
to_char(m[,fmt]) m When converting a numeric value to a string of fmt in a specified format by default, the width of the fmt value can just accommodate all valid numbers
to_number(st[,fmt]) st converts from character data to numeric values in the specified format. By default, the size of the numeric format string is exactly the entire number
Attached:
Format of to_char() function:
---------------------------------
Symbol Description
---------------------------------
9 Each 9 represents a digit in the result
0 represents the pilot to be displayed 0
The $ dollar sign is printed on the left side of the number
L Any local currency symbol
. Print decimal points in decimal
, print the comma representing the thousandths
---------------------------------
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:
avg([distinct/all] n) average value of column n
count([all] *) Returns the number of rows in the query range including duplicate values and null values
count([distinct/all] n) Number of rows with non-null values
max([distinct/all] n) The maximum value of this column or expression
min([distinct/all] n) The minimum value of this column or expression
stdev([distinct/all] n) Standard deviation of this column or expression, null values are ignored
sum([distinct/all] n) The sum of the column or expression
variance([distinct/all] n) The variance of this column or expression, null value is ignored
--------------------------------------------------------------------------------
Date function:
>
add_months(d,n) date d plus n months
last_day(d) The date of the last day of the month containing d
month_between(d,e) The number of months between dates d and e, e precedes d
new_time(d,a,b) date and time of time in time zone a and time in time zone b
next_day(d,day) is later than date d, the date of the week specified by day
sysdate Current system date and time
greatest(d1,d2,...dn) The last date in the given date list
least(d1,k2,...dn) The earliest date in the given date list
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 into date value in the format specified by fmt. If fmt is ignored, st should use the default format
round(d [,fmt]) Date d is rounded to the most recent date in the specified format of fmt
trunc(d [,fmt]) Date d is truncated to the most recent date according to the format specified by fmt
Attached:
Date format:
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
Format code Description Example or range of acceptable values
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
DD One day of the month 1-3
DY The weekly day represented by three capital letters SUN,...SAT
DAY Full weekdays, capital English SUNDAY,...SATURDAY
MM Month 1-12
MON Month represented by three capital letters JAN,...DEC
MONTH full JANUARY,...DECEMBER
RM Roman numerals for the month I,...XII
YY or YYYY Two digits, four digits of year
HH:MI:SS Hours: minutes: seconds
HH12 or HH24 is displayed in 12 hours or 24 hours
MI points
SS seconds
AM or PM PM PM indicator
SP suffix SP requires spelling any numeric field
TH suffix TH means that the added number is an ordinal number 4th, 1st
FM prefix is for month, day or year value, no filling is allowed
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
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;