Oracle常用数学、日期、字符串函数
Oracle常用数学、日期、字符串函数
为了日常使用，这里总结一些常用的oracle函数。
数学函数
| 函数名 | 说明 |
|---|---|
| ABS(n) | 返回数值 n 的绝对值 |
| CEIL(n) | 向上取整 |
| FLOOR(n) | 向下取整 |
| MOD(n1, n2) | 返回n1除以n2的余数 |
| POWER(n1, n2) | 返回n1的n2次方 |
| SIGN(n) | 判断n，大于0返回1，小于0返回-1，等于0返回0 |
| SQRT(n) | 返回n的平方根 |
| ROUND(n) | 对n四舍五入 |
| TRUNC(n1, n2) | 截取精度，n1保留n2个小数 |
例子
select ABS(-5) from dual; -- 返回 5
select ABS(2.35) from dual; -- 返回 3
select POWER(2,5) from dual; -- 返回2的5次方
select TRUNC(3.1415926,2) from dual; -- 返回3.14
日期函数
介绍一些日常用到的函数
| 函数 | 说明 |
|---|---|
| syadate | 系统时间 |
| to_date(ch, fmt) | 将字符串ch，按fmt格式，转成date类型 |
| to_char(n ...) | 是返回字符串；(to_char是用法很多) |
| trunc(date, fmt) | 截断到最接近的日期,单位为天 ,返回的是日期类型 |
| round(date, fmt) | 舍入到最接近的日期 |
| next_day(date,ch) | 返回下个星期的日期, ch为1-7，代表星期日到星期六 |
| last_day(date) | 返回指定日期d所在月份的最后一天 |
| add_months(date,int) | 根据int的正负数，返回int个月之后/之前的date日期时间 |
fmt 日期格式
常见代表符号：yyyy 年，mm 月，dd 日，hh 12小时制，24hh 24小时制，mi 分钟，ss 秒，day 星期
日期函数用于处理date类型的数据，两个日期相减返回日期之间相差的天数。
1、日期和字符串相互转换
select syadate from dual; --获取系统时间
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual; --日期转化为字符串
select to_char(sysdate,'yyyy') from dual; --获取时间的年
select to_char(sysdate,'mm') from dual; --获取时间的月
select to_char(sysdate,'dd') from dual; --获取时间的日
select to_char(sysdate,'hh24') from dual; --获取时间的时
select to_char(sysdate,'mi') from dual; --获取时间的分
select to_char(sysdate,'ss') from dual; --获取时间的秒
-- 返回日期时间类型
select to_date('2004-05-07 13:23:44','yyyy-mm-dd hh24:mi:ss') from dual
to_date(ch, fmt) 是将字符串ch，按fmt格式，转成date类型
to_char 是返回字符串；(to_char是用法很多)
2、求某天是星期几
select to_char(to_date('2022-03-16','yyyy-mm-dd'),'day') from dual; --星期三
3、求两个日期间的天数
select floor(sysdate - to_date('20220306','yyyymmdd')) from dual; --10
4、求当前日期的前一天
select to_char(trunc(sysdate-1),'yyyy-mm-dd') from dual;
5、计算时间差
oracle时间差是以天数为单位,所以换算成年月,日
select floor(to_number(sysdate-to_date('2022-03-16 15:55:03','yyyy-mm-dd hh24:mi:ss'))/365) as spanYears from dual --时间差-年
select ceil(moths_between(sysdate-to_date('2022-03-16 15:55:03','yyyy-mm-dd hh24:mi:ss'))) as spanMonths from dual --时间差-月
select floor(to_number(sysdate-to_date('2022-03-16 15:55:03','yyyy-mm-dd hh24:mi:ss'))) as spanDays from dual --时间差-天
select floor(to_number(sysdate-to_date('2022-03-16 15:55:03','yyyy-mm-dd hh24:mi:ss'))*24) as spanHours from dual --时间差-时
select floor(to_number(sysdate-to_date('2022-03-16 15:55:03','yyyy-mm-dd hh24:mi:ss'))*24*60) as spanMinutes from dual --时间差-分
select floor(to_number(sysdate-to_date('2022-03-16 15:55:03','yyyy-mm-dd hh24:mi:ss'))*24*60*60) as spanSeconds from dual --时间差-秒
6、求月份第一天、最后一天
--这个月的第一天，返回 2022-03-01 00:00:00
SELECT Trunc(SYSDATE, 'MONTH') FROM dual;
-- 这个月的最后一天，返回 2022-03-31 23:59:59
SELECT LAST_DAY(Trunc(SYSDATE, 'MONTH')) + 1 - 1 / 86400 FROM dual;
-- 上个月的第一天，返回 2022-02-01 00:00:00
SELECT Trunc(Trunc(SYSDATE, 'MONTH') - 1, 'MONTH') FROM dual;
-- 上个月的最后一天，返回 2022-02-28 23:59:59
SELECT Trunc(SYSDATE, 'MONTH') - 1 / 86400 FROM dual;
7、add_months() 使用
SELECT add_months(sysdate,2) FROM dual; -- 2022-05-28 11:39:20
select add_months(last_day(sysdate),1) FROM dual; --下个月最后一天的当前时间
select add_months(last_day(sysdate),-1) FROM dual; --上个月的最后一天的当前时间
trunc()函数
在上面例子中，出现了多次trunc() 函数，有必要单独抽出来介绍。
trunc() 函数的功能是用于截取时间或数值，返回指定的值
语法：
-
日期处理：trunc(date,[fmt])
date 为必要参数，是输入的一个date日期值
fmt 参数可忽略，是日期格式，缺省时表示指定日期的0点。 -
数值处理：trunc(number,[decimals])
number 为必要参数，是输入的一个number数值
decimals 参数可忽略，是要截取的位数，缺省时表示截掉小数点后边的值。
例子：
--sysdate和trunc(sysdate)是不一样的 sysdate在当天的零时零分零秒等于trunc(sysdate)
select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'),
to_char(trunc(sysdate), 'yyyy-mm-dd hh24:mi:ss')
from dual t;
--年
select trunc(sysdate, 'yy') "当年第一天" from dual;
select trunc(sysdate, 'yyyy') "当年第一天" from dual;
select trunc(sysdate, 'year') "当年第一天" from dual;
--季度
select trunc(sysdate, 'q') "当前季度的第一天" from dual;
select trunc(add_months(sysdate,3), 'Q') -1/24 "这个季度最后一天23点" from dual;
--月
select trunc(sysdate, 'mm') "当月第一天" from dual;
select trunc(sysdate, 'month') "当月第一天" from dual;
select trunc(last_day(sysdate)+1) "下个月第一天的0点" from dual;
--周
select trunc(sysdate, 'd') "本周的第一天，周日起" from dual;
select trunc(sysdate,'day') "本周的第一天，周日起" from dual;
select next_day(TRUNC(SYSDATE ), 'TUESDAY' ) + 12/24 "下个星期二中午12点" from dual;
select TRUNC(LEAST(NEXT_DAY(SYSDATE, 'SATURDAY'), NEXT_DAY(SYSDATE, 'SUNDAY'))) + （6*60+10）/（24*60） "下个周六日早上6点10分" from dual;
--天
select trunc(sysdate+1) "今晚12点" from dual;
select trunc(sysdate+1) + (8*60+30)/(24*60) "明天早上8点30分" from dual;
--时
select trunc(sysdate, 'hh')"当前小时" from dual;
select trunc(sysdate, 'hh24') "当前小时" from dual;
--分
select trunc(sysdate, 'mi') "当前分钟" from dual;
select trunc(sysdate,'mi') + 10/ (24*60) "10分钟后的时间"from dual;
--秒
select sysdate + 10/(24 * 60 * 60) "10秒钟后" from dual;--trunc没有精确到秒的精度
字符函数
1、字符串截取
select substr('abcdef',1,3) from dual; -- abc
2、查找子串位置
select instr('abcdefgigklmn','de') from dual; -- 4
3、字符串连接
select 'HELLO'||'hello world' from dual; -- HELLOhello world
4、计算字符串长度
select length('abcdefg') from dual; -- 7
5、去掉字符串中的空格
select ltrim(' abc') from dual; -- 去掉左边空格
select rtrim('zhang ') from dual; -- 去掉右边空格
select trim(' zhang san ') from dual; -- 去掉左右两边的空格
6、去掉前缀和后缀
select trim(leading 9 from 998234891299) from dual; --8234891299
select trim(trailing 9 from 998234891299) from dual; --9982348912
select trim(9 from 998234891299) from dual; -- 82348912
7、替换字符
select replace('abcad','a','xyz') from dual; -- xyzbcxyzd
select translate('abcad','1','xyz') from dual; -- xbcxd
8、initcap(ch)首字母大写、 lower(ch)变小写、upper(ch)变大写
select lower('ABC') s1,
upper('def') s2,
initcap('efg') s3
from dual;
9、lpad(expr1, n, expr2) 左添充、 rpad(expr1, n, expr2) 右填充 用于控制输出格式
select lpad('func',10,'=') from dual; -- ======func
select rpad('func',10,'=') from dual; -- func======
