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() 函数的功能是用于截取时间或数值，返回指定的值

语法：

1. 日期处理：trunc(date,[fmt])

   date 为必要参数，是输入的一个date日期值
   fmt 参数可忽略，是日期格式，缺省时表示指定日期的0点。

2. 数值处理：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======