oracle常用函数

– 向上取整
select ceil(10.6) from dual;
– 向下取整
select floor(10.6) from dual;
– 取余
select mod(10,3) from dual;
– n次方
select power(2,3) from dual;
– 四舍五入，保留n位小数
select round(3.55555,2) from dual;
– sign(n) 若n=0,则返回0,否则,n>0,则返回1,n<0,则返回-1
select sign(-10) from dual;
– sqrt(n) n的平方根　　
select sqrt(9) from dual;
– 取整数部分
SELECT trunc(2.111) FROM dual;
– 取绝对值
SELECT abs(-1) FROM dual;
– 取最大值
SELECT greatest(1,2,3,5,10) FROM dual;
– 取最小值
SELECT least(1,2,33,4,-1) FROM dual;
– 处理空值
SELECT nvl(NULL,‘haha’) FROM dual;
– 指定货币(不常用)： $：美元符号 L：当地货币
SELECT to_char(123,‘L999.99’) FROM dual;

– initcap(char) 把每个字符串的第一个字符换成大写
SELECT initcap(‘hello’) FROM dual;
– upper() 把整个字符串转为大写
SELECT upper(‘hellA’) FROM dual ;
– lower(char) 整个字符串换成小写
SELECT lower(‘AkjfhgsK’) FROM dual;
– replace(char,str1,str2) 字符串中所有str1换成str2
SELECT replace(‘seleEct’,‘e’,‘o’) FROM dual;
– substr(char,m,n) 取出从m字符开始的n个字符的子串
SELECT substr(‘helloworld’,2,3) FROM dual;
– 求字符串的长度
SELECT length(‘helloworld’) FROM dual;
– 并置运算符 ||
SELECT ‘he’ || ‘llo’ FROM dual;
– 当前日期和时间
SELECT sysdate FROM dual;
– 本月最后一天
SELECT last_day(sysdate) FROM dual;
– day 星期几
SELECT to_char(sysdate,‘day’) FROM dual;
– 字符串转日期
SELECT TO_DATE(‘20230926’,‘YYYYMMDD’) FROM dual;
– 日期相隔月数
SELECT floor(months_between(sysdate,TO_DATE(‘20230920’,‘YYYYMMDD’))) FROM dual;
– 可获取当前月份之前或者之后的月份且指定格式
select to_char(add_months(sysdate,-1),‘YYYY-MM’) FROM dual;

– 连接字符串
SELECT concat(‘aaaa’,‘bbbb’) test FROM dual;