1、字符
lower :小写
upper :大写
substr:取字符
Exp:SUBSTR('The Three Musketeers',1,3)
instr :返回从第三个字符开始,第二次出现i的位置
Exp:INSTR('Mississippi', 'i',3,2)
initcap:首字母大写
length:查看字符的长度
lengthb:查看字符的长度按照BIT算,中文是2个BIT
ltrim:左边消除指定的字符
lpad :左边补足相应的字符
rtrim :右边消除指定的字符到最后一个未出现的位置为止
Exp:RTRIM('Mississippi','ip')
rpad:右边补足相应的字符,列子补足字段table_name为38位,右边用.补足
RPAD(table_name,38,'.')
Replace:完全匹配的替换相应字符
Exp:REPLACE('uptown','up','down')
Translate:对对应字符进行完全转换、
Exp:TRANSLATE('fumble','uf','aR')
Trim:去掉首尾(也可以只去掉左边和右边的,但是意义不大可以用ltrim和rtrim代替)的相应字符 ,列子中为去掉首尾的. ,默认为去掉首尾的空格
Exp:select length(trim(both '.'from '........woai......')),length(' woai ') from dual;
2、数字
Abs:取绝对值
Exp:abs(-10)
ceil :取整,向正无穷取
Exp:
SQL> select ceil(10.2),ceil(-10.2) from dual;
CEIL(10.2) CEIL(-10.2)
---------- -----------
11 -10
Floor:取整,向负无穷取
Exp:
SQL> select floor(10.2),floor(-10.2) from dual;
FLOOR(10.2) FLOOR(-10.2)
----------- ------------
10 -11
log 取幂
Exp:
SQL> select log(2,64) from dual;
LOG(2,64)
----------
6
mod 取余
Exp:
SQL> select mod(35,8),mod(-49,8) from dual;
MOD(35,8) MOD(-49,8)
---------- ----------
3 -1
power 幂集
Exp:
SQL> select power(8,3) from dual;
POWER(8,3)
----------
512
round 四舍五入
Exp:
SQL> select round(15.555,2),round(15.554,2) from dual;
ROUND(15.555,2) ROUND(15.554,2)
--------------- ---------------
15.56 15.55
lower :小写
upper :大写
substr:取字符
Exp:SUBSTR('The Three Musketeers',1,3)
instr :返回从第三个字符开始,第二次出现i的位置
Exp:INSTR('Mississippi', 'i',3,2)
initcap:首字母大写
length:查看字符的长度
lengthb:查看字符的长度按照BIT算,中文是2个BIT
ltrim:左边消除指定的字符
lpad :左边补足相应的字符
rtrim :右边消除指定的字符到最后一个未出现的位置为止
Exp:RTRIM('Mississippi','ip')
rpad:右边补足相应的字符,列子补足字段table_name为38位,右边用.补足
RPAD(table_name,38,'.')
Replace:完全匹配的替换相应字符
Exp:REPLACE('uptown','up','down')
Translate:对对应字符进行完全转换、
Exp:TRANSLATE('fumble','uf','aR')
Trim:去掉首尾(也可以只去掉左边和右边的,但是意义不大可以用ltrim和rtrim代替)的相应字符 ,列子中为去掉首尾的. ,默认为去掉首尾的空格
Exp:select length(trim(both '.'from '........woai......')),length(' woai ') from dual;
2、数字
Abs:取绝对值
Exp:abs(-10)
ceil :取整,向正无穷取
Exp:
SQL> select ceil(10.2),ceil(-10.2) from dual;
CEIL(10.2) CEIL(-10.2)
---------- -----------
11 -10
Floor:取整,向负无穷取
Exp:
SQL> select floor(10.2),floor(-10.2) from dual;
FLOOR(10.2) FLOOR(-10.2)
----------- ------------
10 -11
log 取幂
Exp:
SQL> select log(2,64) from dual;
LOG(2,64)
----------
6
mod 取余
Exp:
SQL> select mod(35,8),mod(-49,8) from dual;
MOD(35,8) MOD(-49,8)
---------- ----------
3 -1
power 幂集
Exp:
SQL> select power(8,3) from dual;
POWER(8,3)
----------
512
round 四舍五入
Exp:
SQL> select round(15.555,2),round(15.554,2) from dual;
ROUND(15.555,2) ROUND(15.554,2)
--------------- ---------------
15.56 15.55
trunc 截断,不同于四舍五入
Exp:
SQL> select round(15.555,2),round(15.554,2),trunc(15.555,2),round(15.554,2) from dual;
ROUND(15.555,2) ROUND(15.554,2) TRUNC(15.555,2) ROUND(15.554,2)
--------------- --------------- --------------- ---------------
15.56 15.55 15.55 15.55
Exp:
SQL> select round(15.555,2),round(15.554,2),trunc(15.555,2),round(15.554,2) from dual;
ROUND(15.555,2) ROUND(15.554,2) TRUNC(15.555,2) ROUND(15.554,2)
--------------- --------------- --------------- ---------------
15.56 15.55 15.55 15.55
3、日期
add_months:增加相应的月份
Exp:
SQL> select add_months(sysdate,1) from dual;
ADD_MONTHS(SYSDATE,1)
---------------------
2012/5/23 23:18:58
current_date:会话当前时间
Exp:
select sysdate,current_date,sessiontimezone from dual;
CURRENT_TIMESTAMP:会话当前时间带上时区
Exp:
SQL> SELECT CURRENT_TIMESTAMP FROM dual;
CURRENT_TIMESTAMP
-------------------------------------------------
23-APR-12 11.39.00.676507 PM +08:00
EXTRACT:抽取相应的时间部分
Exp:
SELECT SYSDATE
,EXTRACT(YEAR FROM SYSDATE ) YEAR
,EXTRACT(MONTH FROM sysdate) MONTH
,EXTRACT(day FROM sysdate) DAY
FROM dual;
last_day :月的最后一天
Exp:
select last_day(add_months(sysdate,1)) from dual;
add_months:增加相应的月份
Exp:
SQL> select add_months(sysdate,1) from dual;
ADD_MONTHS(SYSDATE,1)
---------------------
2012/5/23 23:18:58
current_date:会话当前时间
Exp:
select sysdate,current_date,sessiontimezone from dual;
CURRENT_TIMESTAMP:会话当前时间带上时区
Exp:
SQL> SELECT CURRENT_TIMESTAMP FROM dual;
CURRENT_TIMESTAMP
-------------------------------------------------
23-APR-12 11.39.00.676507 PM +08:00
EXTRACT:抽取相应的时间部分
Exp:
SELECT SYSDATE
,EXTRACT(YEAR FROM SYSDATE ) YEAR
,EXTRACT(MONTH FROM sysdate) MONTH
,EXTRACT(day FROM sysdate) DAY
FROM dual;
last_day :月的最后一天
Exp:
select last_day(add_months(sysdate,1)) from dual;
months_between:月份相减
Exp:
SQL> select months_between(sysdate,add_months(sysdate,1)) from dual;
MONTHS_BETWEEN(SYSDATE,ADD_MON
------------------------------
-1
next_day:取指定日期的下一个星期几的日期时间
Select NEXT_DAY(sysdate-40,'星期一') From dual ;
Exp:
SQL> select months_between(sysdate,add_months(sysdate,1)) from dual;
MONTHS_BETWEEN(SYSDATE,ADD_MON
------------------------------
-1
next_day:取指定日期的下一个星期几的日期时间
Select NEXT_DAY(sysdate-40,'星期一') From dual ;
4、转换型函数
Round:四舍五入日期时间,可以是天,也可以是小时,分
EXP:
select round(sysdate,'MI') from dual;
select round(sysdate,'HH') from dual;
select round(sysdate) from dual;
EXP:
select round(sysdate,'MI') from dual;
select round(sysdate,'HH') from dual;
select round(sysdate) from dual;
Trunc:截断日期,可以是天,也可以是小时,分
Exp:
select trunc(sysdate,'MI') from dual;
Exp:
select trunc(sysdate,'MI') from dual;
cast:进行数据类型的转化
Exp:
select cast(sysdate as varchar2(100)) from dual;
Exp:
select cast(sysdate as varchar2(100)) from dual;
NUMTODSINTERVAL:将间隔指定为时分秒间隔类型
Exp:
SELECT SYSDATE
,SYSDATE+NUMTODSINTERVAL(2,'HOUR') "2 hours later"
,SYSDATE+NUMTODSINTERVAL(30,'MINUTE') "30 minutes later"
,sysdate+1/12
,sysdate+1/48
FROM dual;
Exp:
SELECT SYSDATE
,SYSDATE+NUMTODSINTERVAL(2,'HOUR') "2 hours later"
,SYSDATE+NUMTODSINTERVAL(30,'MINUTE') "30 minutes later"
,sysdate+1/12
,sysdate+1/48
FROM dual;
NUMTOYMINTERVAL:转化间隔为年月类型
Exp:
SELECT SYSDATE
,SYSDATE+NUMTOYMINTERVAL(2,'YEAR') "2 years later"
,SYSDATE+NUMTOYMINTERVAL(6,'MONTH') "6 months later"
FROM dual;
SELECT SYSDATE
,SYSDATE+NUMTOYMINTERVAL(2,'YEAR') "2 years later"
,SYSDATE+NUMTOYMINTERVAL(6,'MONTH') "6 months later"
FROM dual;
To_char:转化日期和数字类型为字符类型
Exp:
SELECT TO_CHAR(SYSDATE, 'dd" day of "Month, YYYY" Quarter="q" weekday="day') from dual;
SELECT to_char(sysdate,'" time="hh24-mi-ss.sssss" week of month="w" week of year="www') from dual;
select to_char(sysdate, '"day of a week="d" day of year="ddd') from dual;
SELECT SYSDATE
,TO_CHAR(SYSDATE,'Mmspth') Month
,TO_CHAR(SYSDATE,'DDth') Day
,TO_CHAR(SYSDATE,'Yyyysp') Year
,TO_CHAR(SYSDATE,'year') Year
,TO_CHAR(SYSDATE,'YEAR') Year
FROM dual;
Exp:
SELECT TO_CHAR(SYSDATE, 'dd" day of "Month, YYYY" Quarter="q" weekday="day') from dual;
SELECT to_char(sysdate,'" time="hh24-mi-ss.sssss" week of month="w" week of year="www') from dual;
select to_char(sysdate, '"day of a week="d" day of year="ddd') from dual;
SELECT SYSDATE
,TO_CHAR(SYSDATE,'Mmspth') Month
,TO_CHAR(SYSDATE,'DDth') Day
,TO_CHAR(SYSDATE,'Yyyysp') Year
,TO_CHAR(SYSDATE,'year') Year
,TO_CHAR(SYSDATE,'YEAR') Year
FROM dual;
SELECT TO_CHAR(123456,'9.99999EEEE')
,TO_CHAR(123456,'9.9EEEE')
FROM dual;
,TO_CHAR(123456,'9.9EEEE')
FROM dual;
To_number: 转化字符类型为数字类型
TO_DATE:字符类型转换为日期类型
Exp:
SQL> select to_date('day of a week=3 day of year=115', '"day of a week="d" day of year="ddd') from dual;
TO_DATE('DAYOFAWEEK=3DAYOFYEAR
------------------------------
2012/4/24
TO_DATE:字符类型转换为日期类型
Exp:
SQL> select to_date('day of a week=3 day of year=115', '"day of a week="d" day of year="ddd') from dual;
TO_DATE('DAYOFAWEEK=3DAYOFYEAR
------------------------------
2012/4/24
TO_DSINTERVAL:转换为日期可以接受的INTERVAL DAY TO SECOND类型。
SELECT SYSDATE
,SYSDATE+TO_DSINTERVAL('007 12:00:00') "+7 1/2 days"
,SYSDATE+7.5 "+7 1/2 days"
FROM dual;
,SYSDATE+TO_DSINTERVAL('007 12:00:00') "+7 1/2 days"
,SYSDATE+7.5 "+7 1/2 days"
FROM dual;
5、其他类型
Nvl:nvl(x1,x2) x1为空返回x2,不为空就返回x1
Nvl2:nvl2(x1,x2,x3) 当x1是空返回x3,不为空返回x2
Coalesce:返回第一个不为null的值
Exp:
select coalesce('','m') from dual;
Nvl2:nvl2(x1,x2,x3) 当x1是空返回x3,不为空返回x2
Coalesce:返回第一个不为null的值
Exp:
select coalesce('','m') from dual;