Oracle 系统函数

  函数名称 返回值类型 说明 示例
字符串函数 ascii(str) number 返回str首字母的ASCII码 
select ascii('A') from dual; --65
select ascii('ab') from dual; --97 
chr(num) char 返回ASCII码为num的字符 
select chr(65) from dual;--'A'
select chr(97) from dual;--'a' 
concat(p1,p2) char 将p2拼接在p1后边 
select concat(10, 20) from dual; --1020
select concat(10, 'ab') from dual; --10ab
select concat('ab', '10') from dual; --ab10  
initcap(p)  char 将p的首字母大写，其他字母小写  
select initcap('ABCD') from dual; --Abcd
select initcap('abcd') from dual; --Abcd 

instr(p1,p2[,i[,j]])

 number  返回p2中第i位开始到p2结束这个字符串在p1中第j次出现的位置  
select instr('aabbabab', 'ab') from dual; --'ab'在'aabbabab'中第一次出现的位置   2
select instr('aabbabab', 'ab', 3) from dual; --'ab'从'aabbabab'中的第3位开始，第一次出现的位置   5
select instr('aabbabab', 'ab', 3, 2) from dual; --'ab'从'aabbabab'中的第3位开始，第二次出现的位置   7
 instrb（p1,p2[,i[,j]]） number 与函数instr(p1,p2[,i[,j]])的作用相同，但是按字节计算  
select instrb('我我要要我要我要', '我要') from dual; --'我要'在'我我要要我要我要'中第一次出现的位置   3
select instrb('我我要要我要我要', '我要', 4) from dual; --'我要'从'我我要要我要我要'中的第3个字节开始，第一次出现的位置   9
select instrb('我我要要我要我要', '我要', 3, 2) from dual; --'我要'从'我我要要我要我要'中的第3个字节开始，第二次出现的位置   9
 length(p) number 返回参数p的长度值  
select length('abc') from dual; --3
select length(123) from dual; --3 
lengthb(p)  number  返回参数p的字节数  
select lengthb('abc') from dual; --3
select lengthb('我') from dual; --2  
lower(p)  char 将参数p转换为小写  
select lower('ABCD') from dual; --'abcd'
select lower('Ab12') from dual; --'ab12' 
upper(p) char

将参数p转换为大写

 
select upper('abcd') from dual; --'ABCD
select upper('ab12') from dual; --'AB12'
 lpad(p1,i[,p2]) char 在p1的左侧填充p2，使其长度为i  
select lpad(123, 5, 0) from dual; --'00123'
select lpad('abc', 8, 12) from dual; --'12121abc'
select lpad('abc', 8, 'ef') from dual; --'efefeabc'
 ltrim(p1,p2) char 

去掉p1左边开头连续存在的字符p2 

 
select lpad(123, 5, 0) from dual; --'00123'
select lpad('abc', 8, 12) from dual; --'12121abc'
select lpad('abc', 8, 'ef') from dual; --'efefeabc'
 rpad(p1,i[,p2]) char 在p1的右侧填充p2，使其长度为i  
select rpad(123, 5, 0) from dual; --'12300'
select rpad('abc', 8, 12) from dual; --'abc12121'
select rpad('abc', 8, 'ef') from dual; --'abcefefe'
 rtrim(p1,p2) char   去掉p1右边结尾连续存在的字符p2 
select rtrim('aabbcc', 'b') from dual; --'aabbcc'
select rtrim('aabbcc', 'c') from dual; --'aabb'
select rtrim('cdefabab', 'ab') from dual; --'cdef' 
replace(p1,p2[,p3])  char 用p3替换掉p1中存在的p2  
select replace('abcabc', 'c') from dual; --'abab'
select replace('abcabc', 'c', 'd') from dual; --'abdabd' 
 reverse(p) char  返回字符串p的倒叙排列  
select reverse('123') from dual; --'321'
select reverse('abcd') from dual; --'dcba' 
 substr(p1,i[,j]) char 从p1中的i位置开始，截取长度为j的字符串  
select substr('abcdefg', 3) from dual; --'cdefg'
select substr('abcdefg', 2, 3) from dual; --'bcd'
select substr(123456, 2, 4) from dual; --'2345' 
substrb(p1,i[,j])  char 从p中的第i个字节开始，截取长度为j个字节的字符串  
select substrb('123456', 2, 3) from dual; --'234'
select substrb('你好，世界！', 3, 2) from dual; --'好'
select substrb('你好，世界！', 3, 4) from dual; --'好，'
 soundex(p) char 返回由4个字符组成的字符串，其中第一个字符为p的首字母，后3位为数字，可用来表示两个单词发音的相似度（忽略所有元音、字母 y、连写字母和字母 h） 
--两个单词发音一致
select soundex('sheet') from dual; --S300
select soundex('shit') from dual; --S300

--忽略元音
select soundex('ha') from dual; --H000
select soundex('hi') from dual; --H000 
translate(p1,p2,p3)  char 

用p3替换掉p1中存在的p2 

 
select translate('abcabc', 'c', 'd') from dual; --'abdabd' 
 trim(p) char 

删除p首部和尾部的空格 

 　select trim(' abc ') from dual; --'abc'  
正则表达式函数   regexp_like(colname,pattern) 布尔  与like的功能相似，如果colname列的值满足正则表达式regex，则返回该行数据 
--查询数据表emp中ename的值以A、B开头的多有数据行
select * from emp where regexp_like(ename,'^[AB]'); 

 regexp_instr(source,pattern

　　[,position

　　[,occurrence

　　[,return_option

　　[,match_parameter

　　]]]])

 number

与instr的功能相似 ,返回字符串中与正则表达式pattern匹配的字符的位置

 
参数说明：
　　source：需要进行匹配的字符串
　　pattern：正则表达式
　　position：从source中开始匹配的位置
　　return_option：
　　　　0：返回第一个匹配字符出现的位置
　　　　1：返回匹配字符下一个字符的位置
　　math_parameter：
　　　　i：大小写不敏感
　　　　c：大小写敏感(默认值)
　　　　n：.不匹配换行符
　　　　m：多行模式
　　　　x：扩展模式，忽略正则表达式中的空白字符


select regexp_instr('abcd,aabccd,aefdacd','[^,]+') from dual;--1
select regexp_instr('abcd,aabccd,aefdacd','[^,]+',6) from dual;--6
select regexp_instr('abcd,aabccd,aefdacd','[^,]+',1,2) from dual;--6
select regexp_instr('abcd,aabccd,aefdacd','[^,]+',6,2) from dual;--13
select regexp_instr('abcd,aabccd,aefdacd','[^,]+',1,2,0) from dual;--6
select regexp_instr('abcd,aabccd,aefdacd','[^,]+',1,2,1) from dual;--12

 

 

regexp_substr(source,pattern

　　[,position

　　[,occurrence

　　[,match_parameter

　　]]]) 

 char 与substr的功能相似，截取字符串中与正则表达式pattern匹配的字符 
select regexp_substr('abcd,aabccd,aefdacd','[^,]+') from dual;--'abcd'
select regexp_substr('abcd,aabccd,aefdacd','[^,]+',6) from dual;--'aabccd'
select regexp_substr('abcd,aabccd,aefdacd','[^,]+',1,2) from dual;--'aabccd'
select regexp_substr('abcd,aabccd,aefdacd','[^,]+',6,2) from dual;--'aefdacd' 

retexp_replace(source,pattern

　　[,replace_string

　　[,position

　　[,occurrence

　　[,match_parameter

　　]]]]) 

 char 与replace的功能相似 ，将符合正则表达式的字符替换为新的字符 
select regexp_replace('abCdEFGhiJK', '[a-z]') from dual; --'CEFGJK'
select regexp_replace('abCdEFGhiJK', '[a-z]', '*') from dual; --'**C*EFG**JK'
select regexp_replace('abCdEFGhiJK', '[a-z]', '*', 3) from dual; --'abC*EFG**JK'
select regexp_replace('abCdEFGhiJK', '[a-z]', '*', 3, 2) from dual; --'abCdEFG*iJK' 
数字函数 abs(p) number 返回参数的绝对值  
select abs(123) from dual; --123
select abs(-123) from dual; --123 
cos(p) number 返回参数的余弦值  
sin(p) number 返回参数的正弦值  
tan(p) number 返回参数的正切值  
 acos(p) number  反余弦函数，返回-1到1之间的数   
 asin(p) number   反正弦函数，返回-1到1之间的数  
 atan(p) number  反正切函数，返回p的反正切值   
cosh(p) number 返回参数的双曲余弦值  
sinh(p) number 返回参数的双曲正弦值  
tanh(p) number 返回参数的双曲正切值  
 ceil(p) number  返回大于等于p的最小整数  
select ceil(1.3) from dual; --2
select ceil(1.52) from dual; --2 
floop(p) number 返回小于等于p的最大整数 
select floor(1.56) from dual; --1
select floor(1.2) from dual; --1
exp(p)  number  返回e的p次幂，e=2.71828183  　select exp(2) from dual; --7.38905609893065  
ln(p)  number  返回p的自然对数，p>0  　select ln(2.71828183) from dual; --1.00000000056689  
log(i,j)  number   返回以i为底j的对数 　select log(10, 100) from dual; --2  
mod(i,j)  number  返回i除以j的余数  
select mod(10, 3) from dual; --1
select mod(12, 5) from dual; --2 
power(i,j)  number  返回i的j次方  
select power(10, 2) from dual; --100
select power(5, 2) from dual; --25 
round(i[,j])  number  返回对i进行四舍五入后的值，j是保留小数位（可以是0、整数、负数） 
select round(123.456) from dual; --123
select round(123.456, 2) from dual; --123.46
select round(123.456, -2) from dual; --100
select round(152.456, -2) from dual; --200
sign(p)  number  p=0，返回0；p>0，返回1；p<0，返回-1 
select sign(0) from dual; --0
select sign(10) from dual; --1
select sign(-2) from dual; --   -1 
sqrt(p)  number   返回参数的平方根 
select sqrt(100) from dual; --10
select sqrt(25) from dual --   5 
trunc(i,j)  number  返回i保留j位小数的值，不进行四舍五入（j可以是0、整数、负数）  
select trunc(123.456) from dual; --123
select trunc(123.456, 2) from dual; --123.45
select trunc(123.456, -2) from dual; --100 
select trunc(152.456, -2) from dual; --100
转换函数 convert(p,ds[,ss])  char  将参数从ss字符集转换为ds字符集 
select convert('你好', 'utf8', 'zhs16gbk') from dual; --'浣犲ソ'
select convert('浣犲ソ', 'zhs16gbk', 'utf8') from dual; --'你好' 
 hextoraw(p) char  将16进制的参数转换为raw类型  　select hextoraw('abcdef') from dual; --'ABCDEF'  
rawtohex(p)  char  将raw类型的参数转换为16进制  
select rawtohex('AA') from dual; --'4141'
select rawtohex('aa') from dual; --'6161' 
rawidtochar(p)  char  将rawid类型的参数转换为char类型   
cast 不定 将某种类型转换为另一种类型 
select cast('123' as number(6,2)) from dual; --123.00
to_char(p[,fmt])  char  将参数p转换为char类型，如果p为date类型，则可以将其转换为指定格式的字符 
select to_char(123) from dual; --'123'
select to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') from dual; --'2017-09-06 21:48:11' 
to_date(s,fmt)  date  将字符转换为指定格式的date类型 
select to_date('2017-09-06', 'YYYY-MM-DD') from dual; --'2017.9.6
select to_date('09-06-2017', 'MM-DD-YYYY') from dual; --'2017.9.6'
select to_date('2017-09-06 21:48:11', 'YYYY-MM-DD HH24:MI:SS') from dual; --'2017.9.6 21:48:11' 
to_multi_byte(p)  char  将单字节字符转换为双字节字符 
select lengthb('a') from dual; --1
select lengthb(to_multi_byte('a')) from dual; --2
 to_number(p) number  将参数转换为数字 　select to_number('123') from dual; --123  
 to_single_byte() char  将双字节字符转换为单字节字符 
select lengthb('a') from dual; --1
select lengthb(to_multi_byte('a')) from dual; --2
select lengthb(to_single_byte(to_multi_byte('a'))) from dual; --1 
日期函数 add_months(d,i) date 获取在日期d上加i个月后的日期  
select add_months(to_date('2017-09-06', 'YYYY-MM-DD'), 2) from dual; --2017.11.6 
 last_day(d) date  获取日期d中月份最后一天的日期  
select last_day(to_date('2017-02-06', 'YYYY-MM-DD')) from dual; --2017.2.28
select last_day(to_date('2017-09-06', 'YYYY-MM-DD')) from dual; --2017.9.30 
 months_between(d1,d2) number 

获取日期d1、d2相差几个月 

 
select months_between(to_date('2017-02-06', 'YYYY-MM-DD'), to_date('2017-09-06', 'YYYY-MM-DD')) from dual;--  -7
select months_between(to_date('2017-09-06', 'YYYY-MM-DD'), to_date('2017-02-06', 'YYYY-MM-DD')) from dual;--  7 
new_time(d,tz1,tz2) date  将日期d从tz1时区转换为tz2时区  　select new_time(sysdate, 'GMT', 'CST') from dual; --2017.9.6 16:38:47  
next_day(d,w)  date  获取日期d后的下一个星期w（1-7对应周日-周六）  
select next_day(to_date('2017-09-06'), 4) from dual; --'2017.9.13'  
select next_day(to_date('2017-09-06'),'星期三') from dual; --'2017.9.13'
round(d,fmt) date  返回日期d按照指定格式舍入后日期的第一天  
select round(to_date('2017-05-06', 'YYYY-MM-DD'), 'YYYY') from dual; --'2017.1.1'
select round(to_date('2017-09-06', 'YYYY-MM-DD'), 'YYYY') from dual; --'2018.1.1'
select round(to_date('2017-09-06', 'YYYY-MM-DD'), 'MM') from dual; --'2017.9.1'
select round(to_date('2017-09-26', 'YYYY-MM-DD'), 'MM') from dual; --'2018.10.1' 
trunc(d,fmt) date  返回日期d按照指定格式截取后日期的第一天 
select trunc(to_date('2017-05-06', 'YYYY-MM-DD'), 'YYYY') from dual; --'2017.1.1'
select trunc(to_date('2017-09-06', 'YYYY-MM-DD'), 'YYYY') from dual; --'2017.1.1'
select trunc(to_date('2017-09-06', 'YYYY-MM-DD'), 'MM') from dual; --'2017.9.1'
select trunc(to_date('2017-09-26', 'YYYY-MM-DD'), 'MM') from dual; --'2018.9.1' 
sysdate date 获取当前系统时间 
select sysdate from dual; --'2017-09-06 22:13:14'
extract char 截取日期中的指定字段 
-- 获取日期中的年份
select extract(year from sysdate) from dual; --'2017'
select extract(year from date'2011-01-03') from dual; --'2011'
-- 获取日期中的月份
select extract(month from date'2011-01-03') from dual; --'1'
-- 获取日期中的天
select extract(day from date'2011-01-03') from dual; --'3'
聚合函数   avg(col) number  返回col列的平均值 
select avg(sal) from emp;
select avg(sal) from emp group by deptno;
 count(col|*) number  返回列col的行数，*表示所有行 
select count(ename) from emp;
select count(*) from emp group by deptno;
max(col)  不定 返回列col中的最大值  
select max(sal) from emp;
min(col)  不定 返回列col中的最小值  
select min(sal) from emp;
stddev(col)  number  返回列col的标准差，标准差是方差的平方根  
select stddev(sal) from emp;
sum(col)  number  返回列col的总和  
select sum(sal) from emp;
 variance(col) number  返回列col的统计方差  
select variance(sal) from emp;
wm_concat(col)  char  返回列col的合并行，用逗号分隔  
--获取每个部门中的员工姓名
select wm_concat(ename) from emp group by deptno;
   nvl(col,p)  char 如果col的值为空，就用p代替 
select nvl(null,'HH') from dual; --'HH'
   decode(col,p1,s1,p2,s2...sn)  char 为指定列的每个值指定一个对应的新值，如果没有指定  
select e.*,decode(deptno,10,'1班',20,'2班',30,'3班',40,'4班','没有对应班级') from emp e;
结果：
    ... ...  dept  decode
    ... ...    10    1班
    ... ...    20    2班
    ... ...    30    3班
    ... ...    40    4班
    ... ...          没有对应班级
         

  

 

posted @ 2017-09-07 21:30  CS讷于言而敏于行  阅读(190)  评论(0)  编辑  收藏  举报