Oracle常用函数

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;