--返回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
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