最近在学习oracle相关内容,主要关于oracle的函数,以此作为记忆
- 字符函数
(1) initcap(str) 将首字母大写,将其他字母小写
例子 :select initcap('one day') from dual;
结果:One Day
例子 :select initcap('ONE DAY') from dual;
结果:One Day
(2) concat(str1,str2) 对字符串拼接
例子:select concat('One ', 'Day') from dual;
结果:OneDay
(3) length(str) 返回字符数(包含空格,一个汉字算一个)
例子:select length('One Day') from dual;
结果:7
例子:select length('王五') from dual;
结果:2
(4) lengthb(str) 返回字节数
例子:select lengthb('One Day') from dual;
结果:7
例子:select lengthb('王五') from dual;
结果:4
(5) lower(str) 将字符串转换为小写
例子:select lower('One Day') from dual;
结果:one day
(6) upper(str) 将字符串转换为大写
例子:select upper('one Day') from dual;
结果:ONE DAY
(7)replace(x,searchStr,replaceStr) 将x中searchStr替换为replaceStr(区分大小写,不会入库)
例子:select replace('one day', 'o', 'HI') from dual;
结果:HIne day
(8)nvl(x,value) 如果x为null则替换为value,否则为x本身
例子:select nvl(day , 'one') from table;
(9)nvl2(x,value1,value2) 如果x为null则值为value2,否则为value1
例子:select nvl2(day ,'one','two') from table;
(10)trim(trimStr from x) 从字符串两侧去除指定的所有字符串
例子:select trim('a' from 'aaaaaaaaaaaaaaaaOne Dayaaaaaaaaaaaaa') from dual;
结果:One Day
(11)ltrim(x[,trimStr]) 去除字符串左侧指定字符,若没有则去掉左侧空格
例子:select ltrim(' One Day ') from dual;
结果:One Day |
例子:select ltrim('*******One Day********','*') from dual;
结果:One Day********
(12)rtrim(x[,trimStr]) 去除字符串右侧指定字符,若没有则去掉右侧空格
例子:select ltrim(' One Day ') from dual;
结果:| One Day
例子:select ltrim('*******One Day********','*') from dual;
结果:*******One Day
(13)instr(x,findStr[,start][,occurrence]) 返回指定字符串所在位置,可以指定开始位置和返回第几次的结果
例子:select instr('one day day', 'd') from dual;
结果:5
例子:select instr('one day day', 'd', 7) from dual;
结果:9
例子:select instr('one day day', 'd', 1, 2) from dual;
结果:9
(14)substr(x,start[,length]) 返回指定的字符,这些字符从start位置开始,长度为length个字符;如果start是负数,则从x字符串的末尾开始算起;如果length省略,则将返回到末尾的所有字符
例子:select substr('One Day',3) from dual;
结果:e Day
例子:select substr('One Day',-3) from dual;
结果:Day
例子:select substr('One Day', 5, 2) from dual;
结果:Da
例子:select substr('One Day', -5, 3) from dual;
结果:e D
(15)lpad(str,width[,padStr]) 当字符串长度不够时,左侧指定补齐字符,否则空格补齐
例子:select lpad('One Day', 10) from dual;
结果:| One Day
例子:select lpad('One Day', 10, '*') from dual;
结果:***One Day
(16)rpad(str,width[,padStr]) 当字符串长度不够时,左侧指定补齐字符,否则空格补齐
例子:select lpad('One Day', 10) from dual;
结果:One Day |
例子:select lpad('One Day', 10, '*') from dual;
结果:One Day***
- 数值函数
(1) ceil(value) 返回大于等于value的最小整数
例子:select ceil(4.4) from dual;
结果:5
(2)floor(value) 返回小于等于value的最大整数
例子:select floor(4.4) from dual;
结果:4
(3)mod(value1,value2) value1除以value2的余数,若value2=0,则返回value1
例子:select mod(9, 2) from dual;
结果:1
(4)round(value1,value2) 将value1四舍五入,保留小数点后value2位
例子:select round(0.985, 2) from dual;
结果:0.99
例子:select round(0.985) from dual;
结果:1
例子:select round(0.985, -2) from dual;
结果:0
(5)abs(value) 返回value的绝对值
例子:select abs(-1) from dual;
结果:1
(6)trunc(value,n) 对value进行截断,如果n>0,保留n位小数;n<0,则保留-n位整数位;n=0,则去掉小数部分
例子:select trunc(66.985) from dual;
结果:66
例子:select trunc(66.985, 2) from dual;
结果:66.98
例子:select trunc(66.985, -1) from dual;
结果:60
(7)power(m,n) m的n次方
例子:select power(2, 2) from dual;
结果:4
(8)sqrt(n) n的平方根
例子:select sqrt(16) from dual;
结果:4
(9)sign(n) 若n=0,则返回0,若n>0,则返回1,若n<0,则返回-1
例子:select sign(0) from dual;
结果:0
例子:select sign(10) from dual;
结果:1
例子:select sign(-10) from dual;
结果:-1
- 转换函数
(1) to_date(x [,format]) 将x字符串转换为日期
例子:select to_date('2018-12-5', 'YYYY-MM-DD') from dual
结果:2018/12/5
(2)to_number(x [, format]) 将x转换为数字
例子:select to_number('99.9') + 0.1 from dual;
结果:100
例子:select to_number('-$12,345.67', '$99,999.99') from dual;
结果:-12345.67
(3)to_char(x[,format]) 将x转化为字符串。 format为转换的格式,可以为数字格式或日期格式
例子:select to_char('99.99') from dual;
结果:99.99
例子:select to_char('12345.67', '99,999.99') from dual;
结果:| 12,345.67
(4)cast(x as type) 将x转换为指定的兼容的数据库类型
例子:select cast(99.999 as varchar2(10)) from dual;
结果:99.999
例子:select cast('5-12月-18' as date) from dual;
结果:2018/12/5
例子:select cast(99.994 as number(10, 2)) from dual;
结果:99.99
例子:select cast(99.995 as number(10, 2)) from dual;
结果:100.00
- 日期函数
(1)sysdate 当前日期和时间
例子:select sysdate from dual;
结果:2018/12/5 9:39:51
(2)last_day 本月最后一天
例子:select last_day(sysdate) from dual;
结果:2018/12/31 9:53:07
(3)add_months(d,n) 当前日期d后推n个月
例子:select add_months(sysdate, 12) from dual;
结果:2019/12/5 9:54:28
(4)months_between(d,n) 日期d和n相差月数
例子:select months_between(sysdate, to_date('20121205', 'YYYYMMDD')) from dual;
结果:72
(5)next_day(d,day) d后第一周指定day的日期
例子:select next_day(sysdate, '星期一') from dual;
结果:2018/12/10 9:59:11
- 特殊日期函数
(1)Y或YY或YYY ==年的最后一位,两位,三位 ==
例子:select to_char(sysdate,'YYY') from dual;
结果:018
(2)Q 季度,1-3月为第一季度
例子:select to_char(sysdate,'Q') from dual;
结果:4
(3)MM 月份数
例子:select to_char(sysdate, 'MM') from dual;
结果:12
(4)RM 月份的罗马表示
例子:select to_char(sysdate,'RM') from dual;
结果:XII
(5)month 用9个字符表示的月份名
例子:select to_char(sysdate, 'month') from dual;
结果:12月
(6)ww 当年第几周
例子:select to_char(sysdate, 'ww') from dual;
结果:49
(7)w 本月第几周
例子:select to_char(sysdate, 'w') from dual;
结果:1
(8)DDD 当年第几天,一月一日为001 ,二月一日032
例子:select to_char(sysdate, 'DDD') from dual;
结果:339
(9)DD 当月第几天
例子:select to_char(sysdate, 'DD') from dual;
结果:05
(10)D 周内第几天
例子:select to_char(sysdate, 'D') from dual;
结果:4
(11)DY 周内星期几
例子:select to_char(sysdate, 'DY') from dual;
结果:星期三
(12)hh12 12小时制小时数
例子:select to_char(sysdate, 'hh12') from dual;
结果:10
(13)hh24 24小时制小时数
例子:select to_char(sysdate, 'hh24') from dual;
结果:10
(14)ss 秒数
例子:select to_char(sysdate, 'ss') from dual;
结果:10
- 聚集函数
(1)avg(x) 返回x的平均值
例子:select avg(grade) from table;
结果:2
(2)count(x) 返回统计的行数
例子:select count(name) from table;
结果:1
(3)max(x) 返回x的最大值
例子:select max(grade) from table;
结果:100
(4)min(x) 返回x的最小值
例子:select min(grade) from table;
结果:60
(5)sum(x) 返回x的总和
例子:select sum(grade) from table;
结果:240
(6)count([distinct|all]col) 对一列中的值计算个数。distinct去重复,缺省时是ALL
例子:select count(distinct grade) from table;
参考地址:
https://blog.csdn.net/ruiguang21/article/details/80049578
https://www.cnblogs.com/chuangege/p/6258658.html