Oracle内置函数整理

日期函数类型	输入	输出
to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')	select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual	2019-10-30 16:52:22
to_char(sysdate,'yyyy')	select to_char(sysdate,'yyyy') from dual	2019
to_char(sysdate,'yy')	select to_char(sysdate,'yy') from dual	19
to_char(sysdate,'mm')	select to_char(sysdate,'mm') from dual	10
to_char(sysdate,'dd')	select to_char(sysdate,'dd') from dual	30
to_char(sysdate,'hh')	select to_char(sysdate,'hh') from dual	04
to_char(sysdate,'hh24')	select to_char(sysdate,'hh24') from dual	16
to_char(sysdate,'mi')	select to_char(sysdate,'mi') from dual	53
to_char(sysdate,'ss')	select to_char(sysdate,'ss') from dual;	57
to_char(sysdate,'cc')	select to_char(sysdate,'cc') from dual;	21（世纪）
to_char(sysdate,'q')	select to_char(sysdate,'q') from dual;	1(季度)
to_char(int, text)	select to_char(125, '999999') from dual	125
to_char(sysdate,'day')	select to_char(sysdate,'day') from dual	星期几
to_char(sysdate,'d')	select to_char(sysdate,'d') from dual	一周的第几天（周天是第一天）
to_char(sysdate,'DDD')	select to_char(sysdate,'ddd') from dual	年内天
TO_CHAR(SYSDATE,'YEAR','NLS_DATE_LANGUAGE=AMERICAN')	twenty nineteen	转英文
to_char(sysdate,'iw')	select to_char(sysdate,'iw') from dual	年内周
months_between(date,date)	select months_brtween(sysdate，)	俩个日期相差几个月
add_months(date,n)	select add_months(sysdate,3) from dual	当前日期加上3个月的时间
next_day(date,string)	select next_day(sysdate,2) from dual	下一个周1的日期(1-7,分别代表周日到周六)
last_day(date)	select last_day(sysdate) from dual	当前日期月末一天日期（最后一天日期）
TO_CHAR(SYSDATE,'MONTH')	SELECT TO_CHAR(SYSDATE,'MONTH') FROM DUAL	OCTOBER (十月)
TRUNC(SYSDATE,'year')	SELECT TRUNC(SYSDATE,'YEAR') FROM DUAL	本年的第一天
TRUNC(SYSDATE,'q')	SELECT TRUNC(SYSDATE,'MONTH') FROM DUAL	本季度的第一天
TRUNC(SYSDATE,'month')	2019/10/1 星期二	本月的第一天
TRUNC(SYSDATE-1,'w')	2019/10/29 星期二	离当前时间最近的周四
TRUNC(SYSDATE,'day')	2019/10/27 星期日	即上周日
TRUNC(SYSDATE,'iw')	2019/10/28 星期一	本周一

 

字符型单行函数	输入	输出
lower(column|expression)	select lower('HELLO WORD') from dual	hello word（转小写）
upper(column|expression)	select upper('hello word') from dual	HELLO WORD（转大写）
initcap(column|expression)	select initcap('hello word') from dual	Hello Word（首字母大写）
concat(column1,column2)	select concat('hello','word') from dual	helloword（拼接）
substr(column|expression,m[,n])	select substr('helloword',4,2) from dual	lo（截取）
length(column|expression)	select length('hello word') from dual	10（计算长度）
instr(column|expression,'string',[,m],[n])	select instr('struggle for a brtter future','for') from dual	10（字符查找函数）
lapd|rpad(column|expression,n,'string')	select lapd(sal,10,'*' ) from emp	sal为列名，10表示输出结果需要10个字符，不够用*补充
trim(leading|trailing|both,trim_character from trim_source)	select trim('h' from 'helloword') from dual	elloword(替换任意字符串)
trim		去空格（也可以replace替换）
replace(text,search_string,replacement_string)	select replace('hello word','word','boy') from dual	hello boy
数字型单行函数	输入	输出
round(column/expression,n)	select round(3.1415926,3) from dual	3.142（四舍五入）
round(column/expression,n)参数n为负数	select round(-3.1415926,3) from dual	-3.142
trunc(column/expression,n)	select trunc(3.1415926,3) from dual	3.141(截取或者定位日期/不使用四舍五入规则)
trunc(column/expression,n)参数n为负数	select trunc(-3.1415926,3) from dual	3.141
mod(m,n)够除	select mod(1000,300) from dual	100
mod(m,n)不够除	select mod(100,500) from dual	100
日期型单行函数	输入	输出
sysdate	select sysdate from dual;	当天的日期时间
months_between(date,date)	select months_brtween(sysdate，)	俩个日期相差几个月
add_months(date,n)	select add_months(sysdate,3) from dual	当前日期加上3个月的时间
next_day(date,string)	select next_day(sysdate,2) from dual	下一个周2的日期
last_day(date)	select last_day(sysdate) from dual	当前日期月末一天日期
空值处理函数	输入	输出
nvl(expr1,expr2)	select nvl('',4) from dual;	4
nvl2(expr1,expr2,expr3)	select nvl2('',6,3) from dual;	6
nullif(expr1,expr2) 相等返回空值NULL，不等返回expr1	select nullif(2,3) from dual	2
coalesce(expr1,expr2,......,exprn) 返回第一个不为NULL的表达式的值	select coalesce(9,3) from dual	9
replace	SELECT REPLACE(11,1,2) FROM DUAL	22(替换)
trim	去除空格

LISTAGG函数，代替wm_concat

trim 去除空格

字符串中某个字符出现的次数：LENGTH('fdgpfgpp') - LENGTH(REPLACE('fdgpfgpp','p',''))

lead(value,1,null) over(order by ):向上偏移

lag(value,1,null)over(order by ) :向下偏移--也可以加上 partition by