# Johnny_Z

 博客园  :: 首页  :: 新随笔  :: 联系 :: 订阅  :: 管理

(一)字符函数

Upper

SELECT Upper ('abcde') FROM dual  结果：ABCDE

Lower

SELECT lower('ABCDE') FROM dual 结果：abcde

Initcap

SELECT Initcap ('AAA') FROM dual 结果：Aaa

SELECT Initcap ('aaa') FROM dual 结果：Aaa

Concat

SELECT Concat ('a', 'b') FROM dual 结果：ab

Select 'a' || 'b' from dual 结果：ab

Substr

Select substr('abcde',0,3) from dual 结果：abc

Select substr('abcde',-2,3) from dual 结果：de

Length

Select length('abcde') from dual 结果：5

Replace

Select replace('abcde','a','A') from dual 结果：Abcde

Instr

Select instr('Hello World','W') from dual 结果：8

Select instr('Hello World','w') from dual 结果：0

Trim

select trim(' Mr Smith ') from dual 结果：Mr Smith

(二)数值函数

Round

select round(412,-2) from dual;  结果：400

select round(412.713) from dual;结果：413

select round(412,2) from dual;结果：412

select round(412.713,2) from dual;结果：412.71

Mod

select Mod(198,2) from dual 结果：0

Trunc

select trunc(412.13,-2) from dual  结果：400

select trunc(412.13,2) from dual   结果：412.13

select trunc(412.13,1) from dual   结果：412.1

select trunc(412.53) from dual     结果：412

(三)日期函数

Months_between

select months_between(sysdate+35,sysdate) from dual

interval

select   sysdate   +   interval   '3 '   year   from   dual

select   sysdate   +   interval   '3-2 '   year   to   month   from   dual

select   sysdate   +   interval   '10   2:10 '   day   to   minute   from   dual

Next_day

select next_day(sysdate,'星期一') from dual

Last_day

select last_day(sysdate) from dual

(四)转换函数

To_char

select to_char(sysdate,'yyyy-MM-dd') from dual; 结果：2010-10-30

To_number

select to_number('13')+to_number('14') from dual;结果：27

To_date

Select to_date('2012-1-1','yyyy-MM-dd') from dual;结果：2012-1-1

(五)通用函数

NVL

select nvl('string',0) from dual 结果：string

select nvl('',0) from dual 结果：0

NullIF

select nullif('abc','abc') from dual 结果：空

select nullif('abc','abcd') from dual 结果：’abc

NVL2

select nvl2('a','b','c') from dual 结果：b

select nvl2('','b','c') from dual 结果：c

Coalesce

select COALESCE('','','abc','') from dual 结果：abc

select COALESCE('','','','') from dual     结果：空

Case表达式

select case 4

when 10 then

'财务部'

when 20 then

'研发部'

when 30 then

'销售部'

else

'未知部门'

end 部门

from dual

Decode表达式

select decode(10, 10, '财务部', 20, '研发部', 30, '销售部', '未知部门') 部门

from dual;结果：财务部

select decode(2,10,'财务部',decode(3,20,'研发部',decode(4,30,'销售部','未知部门'))) from dual; 结果:未知部门

posted on 2010-10-31 18:31  Johnny_Z  阅读(2039)  评论(0编辑  收藏  举报