oracle常用函数的使用

--返回n1除n2的余数，如果n2=0则返回n1的值
SELECT MOD(2,5) FROM DUAL; 
--ROUND(n1[,n2]) 返回四舍五入小数点右边n2位后n1的值，n2缺省值为0，如果n2为负数就舍入到小数点左边相应的位上
SELECT ROUND(23.56),ROUND(23.56,1),ROUND(23.56,-1) FROM DUAL; 
--TRUNC(n1[,n2] 返回截尾到n2位小数的n1的值，n2缺省设置为0，当n2为缺省设置时会将n1截尾为整数，如果n2为负值，就截尾在小数点左边相应的位上。 
SELECT TRUNC(23.56),TRUNC(23.56,1),TRUNC(23.56,-1) FROM DUAL;
--指定字符串内字符变为小写/大写
SELECT LOWER('WhaT is tHis') FROM DUAL
SELECT UPPER('this is what') FROM DUAL;


--返回指定长度=n的字符串
SELECT LPAD('WhaT is tHis',5),LPAD('WhaT is tHis',25),LPAD('WhaT is tHis',25,'-') FROM DUAL; 
SELECT RPAD('WhaT is tHis',5),RPAD('WhaT is tHis',25),RPAD('WhaT is tHis',25,'-') FROM DUAL; 


--去除空格
SELECT TRIM(' WhaT is tHis ') FROM DUAL; 
SELECT TRIM('W' FROM 'WhaT is tHis w W') FROM DUAL; 
SELECT TRIM(leading 'W' FROM 'WhaT is tHis w W') FROM DUAL; 
SELECT TRIM(trailing 'W' FROM 'WhaT is tHis w W') FROM DUAL; 
SELECT TRIM(both 'W' FROM 'WhaT is tHis w W') FROM DUAL; --跟第一个一样
--从字符串c1左侧截取掉与指定字符串c2相同的字符并返回 没有搞明白
SELECT LTRIM('WWhhhhhaT is tHis w W','aH') FROM DUAL;
SELECT RTRIM('WWhhhhhaT is tHis w W','W H') FROM DUAL;
--替换
SELECT REPLACE('WWhhhhhaT is tHis w W','W','-') FROM DUAL; 
SELECT TRANSLATE('What is this',' ','-') FROM DUAL; 
SELECT TRANSLATE('What is this','-','') FROM DUAL; 
SELECT TRANSLATE('What is this',' ',' ') FROM DUAL; 
SELECT TRANSLATE('What is this','ait','-*') FROM DUAL; 
--神奇的函数啊，该函数返回字符串参数的语音表示形式，对于比较一些读音相同，但是拼写不同的单词非常有用
SELECT SOUNDEX('dog'),soundex('boy') FROM DUAL; 
--截取字符串
SELECT SUBSTR('What is this',5,3) FROM DUAL; 
SELECT SUBSTR('What is this',-5,3) FROM DUAL; 
SELECT SUBSTR('What is this',1,-33) FROM DUAL; 
--字符型函数返回数字值
SELECT INSTR('abcdefg','e',-3),INSTR('abcdefg','e',3) FROM DUAL; 




SELECT LENGTH('abcdefg') FROM DUAL;
--日期
SELECT ADD_MONTHS(sysdate,12),ADD_MONTHS(sysdate,-12) FROM DUAL; 
 alter session set nls_date_format = 'mm-dd-yyyy' ;
select current_date from dual
SELECT SYSDATE,CURRENT_DATE FROM DUAL; 
SELECT last_day(SYSDATE) FROM DUAL; 
SELECT NEXT_DAY(SYSDATE,2) FROM DUAL; 
SELECT NEXT_DAY(SYSDATE,4) FROM DUAL; 


SELECT months_between(SYSDATE, sysdate), months_between(SYSDATE, add_months(sysdate, -1)), 
months_between(SYSDATE, add_months(sysdate, 1)) 
FROM DUAL; 




SELECT ROUND(SYSDATE,'HH24') FROM DUAL; 
SELECT ROUND(SYSDATE) FROM DUAL;
SELECT TRUNC(SYSDATE,'HH24') FROM DUAL; 
SELECT TRUNC(SYSDATE) FROM DUAL; 


--转换函数
SELECT TO_CHAR('AABBCC') FROM DUAL; 
SELECT TO_CHAR(sysdate,'yyyy-mm-dd hh24:mi:ss') FROM DUAL; 
SELECT TO_CHAR(-100, 'L99G999D99MI') FROM DUAL; 


select TO_DATE(5373483, 'J') FROM DUAL; 
SELECT TO_DATE('2007-8-23 23:25:00', 'yyyy-mm-dd hh24:mi:ss') FROM DUAL; 
SELECT TO_CHAR(TO_DATE('9999-12-31','yyyy-mm-dd'),'j') FROM DUAL; 
SELECT TO_NUMBER('-100.00', '9G999D99') FROM DUAL; 


--辅助函数greatest
select decode('a2','a1','true1','a2','true2','default') from dual; 
SELECT GREATEST(15,5,75,8) "Greatest" FROM DUAL; 
SELECT LEAST(15,5,75,8) LEAST FROM DUAL; 
SELECT NULLIF('a','b'),NULLIF('a','a') FROM DUAL; 
SELECT NVL(null, '12') FROM DUAL; 
select nvl2('a', 'b', 'c') isNull,nvl2(null, 'b', 'c') isNotNull from dual; 


SELECT SYS_CONTEXT('USERENV', 'SESSION_USER') FROM DUAL;


SELECT ceil(18.2) FROM DUAL; 




SELECT CHR(95) FROM DUAL; 
SELECT ASCII('_') FROM DUAL; 


SELECT concat('aa','bb') FROM DUAL; 
SELECT INITCAP('whaT is this') FROM DUAL; 
SELECT NLS_INITCAP('中华miNZHu') FROM DUAL


SELECT CURRENT_TIMESTAMP(3) FROM DUAL; 
SELECT LOCALTIMESTAMP(3) FROM DUAL; 


SELECT SYSTIMESTAMP(4) FROM DUAL; 
SELECT DBTIMEZONE FROM DUAL; 
SELECT TO_TIMESTAMP('2007-8-22', 'YYYY-MM-DD HH:MI:SS') FROM DUAL; 
SELECT USER FROM DUAL; 
SELECT VSIZE('abc中华') FROM DUAL