oracle相关总结3-常用函数

1、字符串函数
LENGTH()    字符长度
LENTTHB()    字节长度；一个汉字内存中占用 2字节
LTRIM、RTRIM、TRIM

2、截串
SUBSTR(表达式，位置，长度)
Oracle 无左右取串函数，但可以使用变通方式完成。
左取串：    SUBSTR('abcdefg', 1, 3)

右取串：    SUBSTR('abcedfg', LENGTH('abcdefg')-3+1, 3)

字符函数接受字符输入，并返回字符或数值。
INITCAP(char)                               首字母大写
LOWER(char)                                 转换为小写
UPPER(char)                                 转换为大写
LTRIM(char, set)                            左裁切
RTRIM(char, set)                            右裁切
TRANSLATE(char, from, to)                  按字母翻译
REPLACE(char, search_str, replace_str)    字符串替换
INSTR(char, substr[,pos])                  查找子串位置
SUBSTR(char, pos, len)                      取子字符串
CONCAT(char1, char2)                      连接字符串

CHR(ascii)        根据 ASCII 码返回对应字符串
LPAD / RPAD        左 / 右 填充
 LPAD ('function', 15 , '=') 返回    '=======function'
TRAM            开头或结尾(或 开头和结尾)裁剪特定的字符，默认裁剪空格。
 TRIM ([LEADING | TRAILING] trim_char)
LENGTH(char)        返回字符串长度
DECODE            逐个值进行字符串替换
DECODE (expr, search1, result1, search2, result2, [ ,default])
DECODE (ostalus, 'p', '准备处理', 'c', '已完成')

3、时间函数
sysdate、current_day
设置时间格式：    ALERT SESSION SET NLS_DATE_FORMAT = 'dd-mon-yyyy HH:mi:ss'
求时间：        NEXT_DAY(sysdate, '星期三')
ADD_MONTHS(d, n)    返回 指定日期加上月数后的 日期值
MONTHS_BETWEEN(d1, d2)    返回 两个日期间的 月数
LAST_DAY(d)        返回 指定日期当前的最后一天的 日期值
RONUD(d,[fmt])        返回 指定日期四舍五入格式(YEAR、MONTH、DAY)后的 日期值
NEXT_DAY(d,day)        返回 指定日期下一个星期几的 日期值
TRUNC(d,[fnt])        返回 指定日期截断为格式后的 日期值
EXTRACT(fmt FROM d)    返回 指定日期提取的格式的 值 

4、转换函数
TO_CHAR(sysdate, 'yyyy-mm-dd hh24:mi:ss')
TO_DATE('20130301,'yyyyMMdd hh24:mi:ss')
TO_NUMBER('333')        必须是能转换
TO_TIMESTAMP('2007-10-10 00:00:00.0', 'yyyy-mm-dd hh24:mi:ssxff')    转换为时间戳格式

5、字符型函数

ascii:获得字符的ASCII码,如:Ascii('A') 65
chr:返回与ASCII码相应的字符,如:Chr(65) A
lower:将字符串转换成小写,如:lower ('SQL Course') sql course
upper:将字符串转换成大写,如:upper('SQL Course') SQL COURSE
initcap:将字符串转换成每个单词以大写开头,如:initcap('SQL course') Sql Course
concat:连接两个字符串 concat('SQL', ' Course') SQL Course
substr:给出起始位置和长度，返回子字符串,如:substr('String',1,3) Str
length:求字符串的长度 length('Wellcom') 7
instr:给出起始位置和出现的次数，求子字符串在字符串中出现的位置,如:instr('String', 'r',1,1) 3
lpad:用字符填充字符串左侧到指定长度,如:lpad('Hi',10,'-') --------Hi
rpad:用字符填充字符串右侧到指定长度,如:rpad('Hi',10,'-') Hi--------
trim:在一个字符串中去除另一个字符串,如:trim('S' FROM 'SSMITH') MITH
replace:用一个字符串替换另一个字符串中的子字符串,如:replace('ABC', 'B', 'D') ADC

6、日期函数

months_between:返回两个日期间的
月份,如:months_between ('04-11月-05','11-1月-01'),结果,57.7741935
add_months:返回把月份数加到日期上的新日期,如:add_months('06-2月-03',1),结果,06-3月-03
add_months('06-2月-03',-1),结果,06-1月-03
next_day:返回指定日期后的星期对应的新日期,如:next_day('06-2月-03','星期一'),结果,10-2月-03
last_day:返回指定日期所在的月的最后一天,如:last_day('06-2月-03'),结果,28-2月-03
round:按指定格式对日期进行四舍五入,如:round(to_date('13-2月-03'),'YEAR'),结果,01-1月-03
round(to_date('13-2月-03'),'MONTH'),结果,01-2月-03
round(to_date('13-2月-03'),'DAY'),结果,16-2月-03

(按周四舍五入)
trunc:对日期按指定方式进行截断,如:trunc(to_date('06-2月-03'),'YEAR'),结果,01-1月-03
trunc(to_date('06-2月-03'),'MONTH'),结果,01-2月-03
trunc(to_date('06-2月-03'),'DAY'),结果,02-2月-03

7、其他函数
USER：当前用户
SUM(DECODE(SEX, '男', 1, 0))    筛选出行被为男的记录 并加1
SUM(DECODE(SEX, '女', 1, 0))    筛选出行被为女的记录 并加1
NVL (exp, exp2)        如果 exp 为空返回 exp2；如果非空返回 exp
NVL2 (exp, exp2, exp3)    如果 exp 为空返回 exp3；如果非空返回 exp2
NULLIF (exp1, exp2)    比较两表达式，相等返回空值，不等则返回 exp1

abs:求绝对值函数,如:abs(5) 5
sqrt:求平方根函数,如:sqrt(2) 1.41421356
power:求幂函数,如:power(2,3) 8
cos:求余弦三角函数,如:cos(3.14159) ?1
mod:求除法余数,如:mod(1600, 300) 100
ceil:求大于等于某数的最小整数,如:ceil(2.35) 3
floor:求小于等于某数的最大整数,如:floor(2.35) 2
round:按指定精度对十进制数四舍五入,如:round(45.923, 1),结果,45.9
round(45.923, 0),结果,46
round(45.923, 1),结果,50
trunc:按指定精度截断十进制数,如:trunc(45.923, 1),结果,45.9
trunc(45.923),结果,45
trunc(45.923, 1),结果, 40