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 the end of st2 and st1 (operators available ||)
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;
ab
------- ------------
bald bear bear bear
8.
select name,instr(name,' ') a,instr(name,' ',1,2) b from test;
name ab
--------------------------------------------------------------------------------------------------------------------------------
li lei 3 0
lil 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:
--------------------------------------------------------------------------------------------------------------------------------