1.upper将字符转为大写
SELECTUpper('abddESDamount') damount FROM dual;
2.lower将字符串转为小写
SELECTLOWER('abddESDamount') damount FROM dual;
3.floor取地板,将某个浮点数取整数部分,丢弃小数部分
SELECTFLOOR(23.45)AS amount FROM dual;
4.按指定精度舍入
selectround(55.44,1) roA,ROUND(55.66,1) roA1,round(-55.44,1) roB,ROUND(-55.66,1)roB1 from dual;
时间相关
1.月份的加减
SELECT to_char(add_months(to_date('201712','yyyymm'),1),'yyyymm') A FROM dual;--月份加1 结果 201801
2.获取系统当前时间
select to_char(sysdate,'yyyy-mm-dd day') A from dual;--2018-04-13 星期五
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') A from dual;--2018-04-13 10:50:09
3.字符串转数字
select to_number('2000') usd from dual;
4.group by hiving ,order by asc (desc) ,substr 等在这里不说明了
小技巧
1.不足5位前补0
select replace(lpad('123',5),' ','0') from dual
2.随机时间戳
SELECT to_number(to_char(to_date('2010-01-01', 'yyyy-mm-dd')+DBMS_RANDOM.VALUE(1,(to_char(sysdate, 'J')-to_char(to_date('2010-01-01', 'yyyy-mm-dd'), 'J'))*24*60*60)/3600/24,'yyyymmddhh24miss'))
FROM dual;