Oracle_分类函数大全
Oracle 函数大全
一、字符函数
1、ASCII(n)函数
函数 | ASCII(n) |
描述 | 返回字符串的ascii码(当输入为字符串时返回第一个字符的ascii码) |
实例 | |
备注 |
2、CHR(n)函数
函数 | CHR(n) |
描述 | 返回对应的ascii码的字符(n必须为数字类型) |
实例 | |
备注 |
3、CONCAT(n,m)函数
函数 | CONCAT(n,m) |
描述 | 连接n和m,n和m可以是字符,也可以是字符串。作用和”||”一样。 |
实例 | |
备注 |
4、INITCAP(n)函数
函数 | INITCAP(n) |
描述 | 将字符串n中每个单词首字母大写,其余小写(区分单词的规则是按空格或非字母字符;可以输入中文字符,但没有任何作用) |
实例 | |
备注 |
5、INSTR(chr1,chr2,[n,[m]])函数
函数 | INSTR(chr1,chr2,[n,[m]]) |
描述 | 获取字符串chr2在字符串chr1中出现的位置。n和m可选,省略是默认为1;n代表开始查找的起始位置,当n为负数从尾部开始搜索;m代表字串出现的次数。 |
实例 | |
备注 | 当n为负数从尾部搜索,但返回值仍然是按正向排列得出的位置。 |
6、LENGTH(n)函数
函数 | LENGTH(n) |
描述 | 返回字符或字符串长度。(当n为null时,返回null;返回的长度包括后面的空格) |
实例 | |
备注 |
7、LOWER(n)函数
函数 | LOWER(n) |
描述 | 将n转换为小写。 |
实例 | |
备注 |
8、LPAD(chr1,n,[chr2])函数(与函数19对应)
函数 | LPAD(chr1,n,[chr2]) |
描述 | 在chr1左边填充字符chr2,使得字符总长度为n。chr2可选,默认为空格;当chr1字符串长度大于n时,则从左边截取chr1的n个字符显示。 |
实例 | |
备注 | 不够字符则用*来填满 |
9、LTRIM(chr,[n])函数(与函数20对应)
函数 | LTRIM(chr,[n]) |
描述 | 去掉字符串chr左边包含的n字符串中的任何字符,直到出现一个不包含在n中的字符为止。 |
实例 | |
备注 |
10、NLS_INITCAP(chr,[’nls_param’])函数
函数 | NLS_INITCAP(chr,[’nls_param’]) |
描述 | 将chr首字母大写。Nls_param可选,指定排序的方式。(有SCHINESE_RADICAL_M(部首、笔画),SCHINESE_STROKE_M(笔画、部首),SCHINESE_PINYIN_M(拼音)) |
实例 | |
备注 |
11、NLS_LOWER(chr,[‘nls_param’])函数
函数 | NLS_LOWER(chr,[‘nls_param’]) |
描述 | 将字符串转换为小写。Nls_param可选,指定排序的方式。(有SCHINESE_RADICAL_M(部首、笔画),SCHINESE_STROKE_M(笔画、部首),SCHINESE_PINYIN_M(拼音)) |
实例 | |
备注 |
12、NLSSORT(col,[’nls_param’])函数
函数 | NLSSORT(col,[’nls_param’]) |
描述 | 根据nls_param指定的方式对col字段进行排序 |
实例 | |
备注 | 拼音 SELECT * FROM TEAM ORDER BY NLSSORT(队名,'NLS_SORT = SCHINESE_PINYIN_M')
笔划 SELECT * FROM TEAM ORDER BY NLSSORT(队名,'NLS_SORT = SCHINESE_STROKE_M') 部首 SELECT * FROM TEAM ORDER BY NLSSORT(队名,'NLS_SORT = SCHINESE_RADICAL_M') |
13、NLS_UPPER(chr,[‘nls_param’])函数
函数 | NLS_UPPER(chr,[‘nls_param’]) |
描述 | 将chr转换为大写。Nls_param可选,用于指定排序规则 |
实例 | |
备注 |
14、REGEXP_REPLACE(source_string,pattern,replace_string,position,occurtence, match_parameter) 函数(10g新函数)
函数 | REGEXP_REPLACE |
描述 | 字符串替换函数。相当于增强的replace函数。Source_string指定源字符表达式;pattern指定规则表达式;replace_string指定用于替换的字符串;position指定起始搜索位置;occurtence指定替换出现的第n个字符串;match_parameter指定默认匹配操作的文本串。 |
实例 | select regexp_replace('abc12hdjj2345jsdsjh456789jdsk','([0-9]+)',lpad('\1',10,'0')) from dual |
备注 | 其中replace_string,position,occurtence,match_parameter参数都是可选的 |
15、REGEXP_SUBSTR(source_string, pattern[,position [, occurrence[, match_parameter]]]) 函数(10g新函数)
函数 | REGEXP_SUBSTR |
描述 | 返回匹配模式的子字符串。相当于增强的substr函数。Source_string指定源字符表达式;pattern指定规则表达式;position指定起始搜索位置;occurtence指定替换出现的第n个字符串;match_parameter指定默认匹配操作的文本串。 |
实例 | select REGEXP_SUBSTR('2222 333 444','(^| )[^ ]+',1,1) from dual; |
备注 | 其中position,occurtence,match_parameter参数都是可选的 |
16、REGEXP_LIKE(source_string, pattern[, match_parameter])函数(10g新函数)
函数 | REGEXP_LIKE |
描述 | 返回满足匹配模式的字符串。相当于增强的like函数。Source_string指定源字符表达式;pattern指定规则表达式;match_parameter指定默认匹配操作的文本串。 |
实例 | |
备注 | 其中position,occurtence,match_parameter参数都是可选的 |
17、REGEXP_INSTR(source_string, pattern[, start_position[, occurrence[, return_option[, match_parameter]]]])函数(10g新函数)
函数 | REGEXP_INSTR |
描述 | 该函数查找 pattern ,并返回该模式的第一个位置。您可以随意指定您想要开始搜索的 start_position。 occurrence 参数默认为 1,除非您指定您要查找接下来出现的一个模式。return_option 的默认值为 0,它返回该模式的起始位置;值为 1 则返回符合匹配条件的下一个字符的起始位置 |
实例 | |
备注 |
18、REPLACE(chr,search_string,[,replacement_string])函数
函数 | REPLACE |
描述 | 将chr中满足search_string条件的替换为replacement_string指定的字符串,当search_string为null时,返回chr;当replacement_string为null时,返回chr中截取掉search_string部分的字符串。 |
实例 | |
备注 |
19、RPAD(chr1,n,chr2)函数
函数 | RPAD(chr1,n,chr2) |
描述 | 在chr1右边填充chr2,使返回字符串长度为n..当chr1长度大于n时,返回左端n个字符。 |
实例 | |
备注 | 参考LPAD()函数。 |
20、RTRIM(chr,[set])函数
函数 | RTRIM(chr,[set]) |
描述 | 去掉chr右边包含的set中的任何字符,直到出现一个不是set中的字符结束。 |
实例 | |
备注 | 参考LTRIM()函数 |
21、SOUNDEX(chr)函数
函数 | SOUNDEX(chr) |
描述 | 返回字符串的语音表示,可以用来比较字符串的发音是否相同。 |
实例 | select xm from table1 where soundex(xm)=soundex('weather');
XM -------- weather wether |
备注 |
22、SUBSTR(chr,m[,n])函数
函数 | SUBSTR(chr,m[,n]) |
描述 | 取chr的子串。M代表开始位置,n是要取的长度。当m为0时从首字符开始,当m为负时从字符串尾部开始截取。 |
实例 | |
备注 | 注意:m取0或1时,开始位置是一样的,都是从第一位开始,m为负的时候,仍然是按从左到右的顺序取,所以如果m为-1,n的长度再大,也只能取到最后一个字符,因为chr右边已经没有字符了。 |
23、TRANSLATE(chr,from_str,to_str)函数
函数 | TRANSLATE(chr,from_str,to_str) |
描述 | 另一种替换函数的用法 |
实例 | |
备注 | 匹配的规则是from_str和to_str每个字符按顺序相对应,如果from_str字符少于to_str中的字符,则只替换能对应的字符,to_str后面不能和from_str对应的字符则不管,如果from_str字符多于to_str字符,则from_str中找不到对应字符按照null来处理。 |
24、TRIM(chr)函数
函数 | TRIM(chr) |
描述 | TRIM函数将字符串的前缀(或尾随)字符删除。
其具体的语法格式如下: TRIM([LEADING|TRAILING|BOTH][trimchar FROM] string) 其中: LEADING 指明仅仅将字符串的前缀字符删除 TRAILING 指明仅仅将字符串的尾随字符删除 BOTH 指明既删除前缀字符,也删除尾随字符。这也是默认方式 string 任意一待处理字符串 trimchar 可选项。指明试图删除什么字符,默认被删除的字符是空格 下面是该函数的使用情况: TRIM(’ Ashley ’)=‘Ashley’ TRIM(LEADING ’*’ FROM’***Ashley***’)=‘Ashley***’ |
实例 | |
备注 |
25、UPPER(chr)函数
函数 | UPPER(chr) |
描述 | UPPER函数间返回字符串的大写形式。 |
实例 | |
备注 |
二、单值函数
1、ABS(n) 函数
函数 | ABS(n) |
描述 | 返回数字的绝对值 |
实例 | |
备注 |
2、COS(n) 函数
函数 | COS(n) |
描述 | 返回n的余弦值 |
实例 | |
备注 |
3、ACOS(n) 函数
函数 | ACOS(n) |
描述 | 反余弦函数,n between -1 and 1,返回值between 0 and pi。 |
实例 | |
备注 |
4、BITAND(n1,n2) 函数
函数 | BITAND(n1,n2) |
描述 | 位与运算 |
实例 | |
备注 | 假设3,9做位与运算,3的二进制形式为:0011,9的二进制形式为:1001,则结果是0001,转换成10进制数为1。 |
5、CEIL(n)函数
函数 | CEIL(n) |
描述 | 返回大于或等于n的最小的整数值 |
实例 | |
备注 |
6、FLOOR(n)函数
函数 | FLOOR(n) |
描述 | 返回小于或等于n的最小的整数值 |
实例 | |
备注 |
7、BIN_TO_NUM(n1,n2,....n)函数
函数 | BIN_TO_NUM(n1,n2,....n) |
描述 | 二进制转向十进制 |
实例 | |
备注 |
8、SIN(n) 函数
函数 | SIN(n) |
描述 | 返回n的正玄值,n为弧度 |
实例 | |
备注 |
9、SINH(n)函数
函数 | SINH(n) |
描述 | 返回n的双曲正玄值,n为弧度 |
实例 | |
备注 |
10、ASIN(n) 函数
函数 | ASIN(n) |
描述 | 反正玄函数,n between -1 and 1,返回值between pi/2 and -pi/2。 |
实例 | |
备注 |
11、TAN(n) 函数
函数 | TAN(n) |
描述 | 返回n的正切值,n为弧度 |
实例 | |
备注 |
12、TANH(n) 函数
函数 | TANH(n) |
描述 | 返回n的双曲正切值,n为弧度 |
实例 | |
备注 |
13、ATAN(n) 函数
函数 | ATAN(n) |
描述 | 反正切函数,n表示弧度,返回值between pi/2 and -pi/2。 |
实例 | |
备注 |
14、EXP(n) 函数
函数 | EXP(n) |
描述 | 返回e的n次幂,e = 2.71828183 ... |
实例 | |
备注 |
15、LN(n) 函数
函数 | LN(n) |
描述 | 返回n的自然对数,n>0 |
实例 | |
备注 |
16、LOG(n1,n2)函数
函数 | LOG(n1,n2) |
描述 | 返回以n1为底n2的对数,n1 >0 and not 1 ,n2>0 |
实例 | |
备注 |
17、POWER(n1,n2) 函数
函数 | POWER(n1,n2) |
描述 | 返回n1的n2次方。n1,n2可以为任意数值,不过如果m是负数,则n必须为整数 |
实例 | |
备注 |
18、SIGN(n)函数
函数 | SIGN(n) |
描述 | 如果n<0返回-1,如果n>0返回1,如果n=0返回0 |
实例 | |
备注 |
19、SQRT(n) 函数
函数 | SQRT(n) |
描述 | 返回n的平方根,n为弧度。n>=0 |
实例 | |
备注 |
20、TRUNC函数
函数 | TRUNC |
描述 | 按照指定的精度截取一个数
|
实例 | |
备注 |
1、greatest 函数
函数 | greatest |
描述 | 取一个一维数组中的最大值greatest |
实例 | |
备注 |
2、least 函数
函数 | least |
描述 | 取一个一维数组中的最小值least |
实例 | |
备注 |
TO_DATE格式
Day:
dd number 12
dy abbreviated fri
day spelled out friday
ddspth spelled out, ordinal twelfth
Month:
mm number 03
mon abbreviated mar
month spelled out march
Year:
yy two digits 98
yyyy four digits 1998
24小时格式下时间范围为: 0:00:00 - 23:59:59....
12小时格式下时间范围为: 1:00:00 - 12:59:59 ....
1、日期和字符转换函数用法(to_date,to_char)
函数 | to_date,to_char |
描述 | 日期和字符转换 |
实例 | |
备注 |
2、 求某天是星期几
函数 | to_date,to_char |
描述 | 求某天是星期几 |
实例 |
|
备注 |
3、monday 设置日期语言
函数 | monday |
描述 | 显示日期格式 |
实例 |
|
备注 | TO_DATE ('2002-08-26', 'YYYY-mm-dd', 'NLS_DATE_LANGUAGE = American') |
4、两个日期间的天数
函数 | floor |
描述 | 两个日期间的天数 |
实例 | |
备注 |
- 时间为null的用法
函数 | To_date |
描述 | To_date(null) |
实例 | |
备注 |
6、组合用法
函数 | To_date |
描述 | 查找2008-07-28至2008-07-01间除星期一和七的天数 |
实例 | |
备注 |
- 7. months_between
函数 | months_between |
描述 | 计算日期 |
实例 |
|
备注 |
- 8. Next_day的用法(24同解)
函数 | Next_day(date, day) |
描述 | 准确来说一个礼拜是从星期日开始到星期六结束的,很多的查询条件和统计都需要求得一周的时间段,也就是星期一到星期日的时间段, 我们用to_char和next_day两个函数分别可以求得第一天和最后一天的日期。 |
实例 |
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD') 今天, '星期'||SUBSTR('日一二三四五六',TO_NUMBER(TO_CHAR(SYSDATE,'D')),1) 星期几, TO_CHAR(NEXT_DAY(sysdate,'星期一')-7,'YYYY-MM-DD') 星期一, TO_CHAR(NEXT_DAY(sysdate,'星期一')-1,'YYYY-MM-DD') 星期日 FROM DUAL; (未成功执行) |
备注 | ** 使用TO_CHAR(SYSDATE,'D')可以求得当前日期是一周的第几天,得到的结果是星期日开始作为第1天的,那么星期一就是第2天,星期日就是第8天 */
/** SUBSTR代替DECODE函数, NEXT_DAY 函数可以指定当前日期的下一个星期几的日期 ,比如:今天是11-25日,星期六,那么 NEXT_DAY(sysdate,'星期一') , 得到的日期就是11-27,那么这个星期一的日期就是 11-27 减去 7天, 而星期日的日期就是11-27减去1天 */ |
9、获得时间
函数 | To_char |
描述 | 获得小时数,获取年月日与此类似 |
实例 | |
备注 |
- 10. 处理月份天数不定的办法
函数 | Last_day |
描述 | 处理月份天数不定的办法 |
实例 | |
备注 |
11、找出今年的天数
函数 | add_months,trunc |
描述 | 找出今年的天数 |
实例 | |
备注 | 闰年的处理方法
to_char( last_day( to_date('02' || :year,'mmyyyy') ), 'dd' ) 如果是28就不是闰年 |
12、不同时区的处理
函数 | NEW_TIME |
描述 | 不同时区的处理 |
实例 | |
备注 |
13、 5秒钟一个间隔
函数 | FLOOR,TO_CHAR |
描述 | 5秒钟一个间隔 |
实例 | |
备注 | SSSSS表示5位秒数 |
14、一年的第几天
函数 | TO_CHAR |
描述 | 一年的第几天 |
实例 | |
备注 |
15、计算小时,分,秒,毫秒
函数 | TRUNC |
描述 | 计算小时,分,秒,毫秒 |
实例 | |
备注 |
16、add_months()
函数 | add_months() |
描述 | 用于从一个日期值增加或减少一些月份 |
实例 | |
备注 |
17、current_date()
函数 | current_date() |
描述 | 返回当前会放时区中的当前日期,date_value:=current_date |
实例 | |
备注 |
18、current_timestamp()
函数 | current_timestamp() |
描述 | 以timestamp with time zone数据类型返回当前会放时区中的当前日期
timestamp_with_time_zone_value:=current_timestamp([timestamp_precision]) |
实例 | |
备注 |
19、dbtimezone()
函数 | dbtimezone() |
描述 | 返回时区 ,varchar_value:=dbtimezone |
实例 | |
备注 |
20、extract()
函数 | extract() |
描述 | 找出日期或间隔值的字段值
date_value:=extract(date_field from [datetime_value|interval_value]) |
实例 | |
备注 |
21、last_day()
函数 | last_day() |
描述 | 返回包含了日期参数的月份的最后一天的日期,date_value:=last_day(date_value) |
实例 | |
备注 |
22、localtimestamp()
函数 | localtimestamp() |
描述 | 返回会话中的日期和时间,timestamp_value:=localtimestamp |
实例 | |
备注 |
23、months_between()
函数 | months_between() |
描述 | 判断两个日期之间的月份数量
number_value:=months_between(date_value,date_value) |
实例 | |
备注 |
24.next_day函数
函数 | next_day |
描述 | next_day(sysdate,6)是从当前开始下一个星期五。后面的数字是从星期日开始算起。 |
实例 | 1.查询某周的第一天
|
备注 | 2.查询某周的最后一天
select trunc(decode(ww, 53, to_date(yy || '3112', 'yyyyddmm'), to_date(yy || '-' || to_char(ww * 7), 'yyyy-ddd')), 'd') - 6 first_day from (select substr('2004-33', 1, 4) yy, to_number(substr('2004-33', 6)) ww from dual); select trunc(to_date(substr('2003-01',1,5)||to_char((to_number(substr('2003-01',6)))*7), 'yyyy-ddd'),'d') last_day from dual; select max(v_date) from (select (to_date('200408','yyyymm') + rownum) v_date from all_tables where rownum < 370) where to_char(v_date,'yyyy-iw') = '2004-33'; 3.查询某周的日期 select min_date, to_char(min_date,'day') day from (select to_date(substr('2004-33',1,4)||'001'+rownum-1,'yyyyddd') min_date from all_tables where rownum <= decode(mod(to_number(substr('2004-33',1,4)),4),0,366,365) union select to_date(substr('2004-33',1,4)-1|| decode(mod(to_number(substr('2004-33',1,4))-1,4),0,359,358)+rownum,'yyyyddd') min_date from all_tables where rownum <= 7 union select to_date(substr('2004-33',1,4)+1||'001'+rownum-1,'yyyyddd') min_date from all_tables where rownum <= 7 ) where to_char(min_date,'yyyy-iw') ='2004-33'; |
1、to_char 将DATE或者BUMBER 转换为字符串(参看日期函数)
2、to_date 将nmber、char、vachar2转换为date(参看日期函数)
3、to_number将char、varchar2转换为number
函数 | to_number |
描述 | 将char、varchar2转换为number |
实例 | |
备注 |
- TRANSLATE
函数 | TRANSLATE |
描述 | 逐个字符替换
|
实例 | |
备注 |
- decode
DECODE函数相当于一条件语句(IF).它将输入数值与函数中的参数列表相比较,根据输入值返回一个对应值。函数的参数列表是由若干数值及其对应结果值组成的若干序偶形式。当然,如果未能与任何一个实参序偶匹配成功,则函数也有默认的返回值。区别于SQL的其它函数,DECODE函数还能识别和操作空值. 其具体的语法格式如下:
DECODE(input_value,value,result[,value,result…][,default_result]);
其中: input_value 试图处理的数值。DECODE函数将该数值与一系列的序偶相比较,以决定最后的返回结果,value 是一组成序偶的数值。如果输入数值与之匹配成功,则相应的结果将被返回。对应一个空的返回值,可以使用关键字NULL于之对应 ,result 是一组成序偶的结果值 ,default_result 未能与任何一序偶匹配成功时,函数返回的默认值
下面的例子说明了,如何读取用户CHECKUP表SEAPARK中的BLOOD_TEST_FLAG列下的项目,作为DECODE函数的实参支持值。
SELECT checkup_type,
DECODE(blood_test_flag,’Y’,’Yes’,’N’,’No’,NULL,’None’,’Invalid’)
FROM checkup;
1、AVG(DISTINCT|ALL)
函数 | AVG(DISTINCT|ALL) |
描述 | all表示对所有的值求平均值,distinct只对不同的值求平均值 |
实例 |
|
备注 |
2、MAX(DISTINCT|ALL)
函数 | MAX(DISTINCT|ALL) |
描述 | 求最大值,ALL表示对所有的值求最大值,DISTINCT表示对不同的值求最大值,相同的只取一次 |
实例 | |
备注 |
3、MIN(DISTINCT|ALL)
函数 | MIN(DISTINCT|ALL) |
描述 | 求最小值,ALL表示对所有的值求最小值,DISTINCT表示对不同的值求最小值,相同的只取一次 |
实例 | |
备注 |
4、STDDEV(distinct|all)
函数 | STDDEV(distinct|all) |
描述 | 求标准差,ALL表示对所有的值求标准差,DISTINCT表示只对不同的值求标准差 |
实例 |
|
备注 |
5、VARIANCE(DISTINCT|ALL)
函数 | VARIANCE(DISTINCT|ALL) |
描述 | 求协方差 |
实例 | |
备注 |
6、GROUP BY
函数 | GROUP BY |
描述 | 主要用来对一组数进行统计 |
实例 |
|
备注 |
7、HAVING
函数 | HAVING |
描述 | 对分组统计再加限制条件 |
实例 | |
备注 |
8、ORDER BY
函数 | ORDER BY |
描述 | 用于对查询到的结果进行排序输出 |
实例 | |
备注 |