Oracle SQL函数大全

1、-SQL函数-数字函数-返回数字n的绝对值ABS(n-三角函数ACOS(nASIN(nATAN(nSIN(n-正弦COS(n-余弦-返回大于或等于n的最小整数CEIL(n-返回e的n次幂EXP(n-返回小于或等于n的最大整数FLOOR(n-返回n的自然对数,n 必须大于0LN(n-返回 m 为底的 n 的对数LOG(m , n-返回两个数取模MOD(m , n-返回四舍五入ROUND(n , mSQL>select round(12635.714265,0,round(12635.714265,-3,round(12635.714265,3from dual;-检测数字的正负,正返回 1

2、,负返回 -1,0 返回 0;GN(n-返回n的平方根SQRT(n-截取数字TRUNC(n , mSQL>select trunc(1245.567,trunc(1245.567,2,trunc(1245.567,-2from dual;TRUNC(1245.567 TRUNC(1245.567,2 TRUNC(1245.567,-2- - -12451245.561200-字符函数-连接字符串CONNECT(char1 , char2-将字符char中的首字符大写,其他字符小写INITCAP(char-将字符串转换为小写LOWER(char-将字符串转换为大写UPPER(char-取子

3、串在字符串中的位置INSTR(char1, char2 ,n, mSQL>select instr('this is apper','is',1,1from dual;INSTR('THISISAPPER','IS',1,1-3SQL>select instr('is this is apper','is',1,1from dual;INSTR('ISTHISISAPPER','IS',1,1-1SQL>select instr('is th

4、is is apper','is',1,3from dual;INSTR('ISTHISISAPPER','IS',1,3-9-取字符换的长度LENGTH(char-在字符串1 左端和右端填充字符串2 直到长度达到n 为止,若char1 的长度> n 均返回char1 左端的n 个字符LPAD(char1 , n, char2RPAD(char1 , n, char2-去除char1 左端包含的set 中的任何字符LTRIM(char1 ,set-去除char1 两端特定的字符TRIM(char from char1SQL>s

5、elect trim('o'from'i love you !'from dual;TRIM('O'FROM'ILOVEYOU!'-i love you !SQL>select trim('o'from'oi love youo !'from dual;TRIM('O'FROM'OILOVEYOUO!'-i love youo !SQL>select trim('o'from'oi love youo'from dual;T

6、RIM('O'FROM'OILOVEYOUO'-i love you-替换子串REPLACE(char, search_string , replacement_stringSQL>select replace('this is apple','apple','orange'from dual;REPLACE('THISISAPPLE','APPLE',-this is orange-日期时间函数-返回特定日期时间d 之后或之前的 n 个月对应的日期时间ADD_MONTHS(s

7、ysdate, n-返回当前回话所对应的日期时间CURRENT_DATESQL>select current_date from dual;CURRENT_DATE-2012/5/1518CURRENT_TIMESTAMPSQL>select current_timestamp from dual;CURRENT_TIMESTAMP-取出日期中的特定数据EXTRACTSQL>select extract(year from sysdatefrom dual;EXTRACT(YEARFROMSYSDATE-2012-返回日期所在月份的最后一天LAST_DAY(dSQL>s

8、elect LAST_DAY(sysdatefrom dual;LAST_DAY(SYSDATE-2012/5/3119:00:3-返回日期相差的月数MONTHS_BETWEEN(d1 , d2SQL>select months_between(sysdate,'15-3月-12'from dual;MONTHS_BETWEEN(SYSDATE,'15-3月-12'-2-截断日期时间数据TRUNC(d , fmtSQL>select trunc(sysdate,'year'from dual;TRUNC(SYSDATE,'YE

9、AR'-2012/1/1-转换函数-返回匹配特定表达式的结果DECODE(value, if1, then1, if2,then2, if3,then3, . . . default-将特定数字格式的字符串转变成数值型TO_NUMBER(CHAR, fmt , nls_paramTO_NUMBER('1000.22','99999D99'-1000.22-将日期型转化为字符串 TO_CHAR(date ,fmt , nls_param -将数值型转变为varchar2 数据类型 TO_CHAR(n ,fmt , nls_param -将字符串转变为CLOB 类型 TO_CLOB(char SQL> select to_clob(n'华东交通大学' from dual; TO_CLOB(N'华东交通大学' -华东交通大学 -转变为日期型 to_date(CHAR , fmt , nls_param SQL> select to_date('05-15-12' , 'MM-DD-YY' from dual; TO_DATE('05-15-12','MM-DD-YY