oracle 函数

--(1) ASCII 函数
SELECT ascii ('a') FROM dual; --97
--(2) CHR 函数
SELECT chr(97) FROM dual; --'a'
--(3) CONCAT 函数
SELECT concat ('a','b') FROM dual; --'ab'
--(4) INITCAP
SELECT initcap ('abc') FROM dual; --'Abc'
--(5) INSTR
SELECT instr('oracle traning','ra',1,2) FROM dual; --9
--(6) LENGTH
SELECT length('abcde') FROM dual; --5
--(7) LOWER
SELECT lower('ABC') FROM dual; --abc
--(8) UPPER
SELECT upper('abc') FROM dual; --ABC
--(9) RPAD
SELECT rpad('abc',5,'@') FROM dual; --abc@@
--(10) LPAD
SELECT Lpad('abc',5,'@') FROM dual; --@@abc
--(11) LTRIM
SELECT LTRIM(' abc') FROM dual; --abc
--(12) RTRIM
SELECT RTRIM('abc ') FROM dual; --abc
--(13) SUBSTR
SELECT SUBSTR('123456',2,3) FROM dual; --234
--(14) REPLACE
SELECT REPLACE('abcabceeeeee','bc','$') FROM dual; --a$a$eeeeee
--(15) TRIM
SELECT TRIM('s' from 'strings') FROM dual; --tring
--(16) ABS
SELECT ABS(-123) FROM dual; --123
--(17) CEIL
SELECT ceil(13.45) FROM dual; --14
SELECT ceil(13.55) FROM dual; --14
SELECT ceil(-13.45) FROM dual; -- -13
SELECT ceil(-13.55) FROM dual; -- -13
--(18) FLOOR
SELECT FLOOR(13.45) FROM dual; --13
SELECT FLOOR(13.55) FROM dual; --13
SELECT FLOOR(-13.45) FROM dual; -- -14
SELECT FLOOR(-13.55) FROM dual; -- -14
--(19) ROUND
SELECT ROUND(13.45) FROM dual; --13
SELECT round(13.55) FROM dual; --14
SELECT round(-13.45) FROM dual; -- -13
SELECT round(-13.55) FROM dual; -- -14
--(20) TRUNC
SELECT TRUNC(13.45) FROM dual; --13
SELECT TRUNC(13.55) FROM dual; --13
SELECT TRUNC(-13.45) FROM dual; -- -13
SELECT TRUNC(-13.55) FROM dual; -- -14
--(21) EXP
SELECT exp(2) FROM dual; --7.38905609893065
--(22) POWER
SELECT power(10,2) FROM dual; --100
--(23) SQRT
SELECT sqrt(100) FROM dual; --10
--(24) ADD_MONTHS
select to_char(sysdate,'dd-mm-yyyy day') from dual; --25-01-2011 星期二
SELECT add_months(to_date('199501','yyyymm'),3) FROM dual;--1995-4-1
select to_char(sysdate,'yyyy.mm.dd'),to_char((sysdate)+1,'yyyy.mm.dd') from dual; -- 2011.01.25 2011.01.26
select last_day(sysdate) from dual; -- 2011-1-31 上午 11:42:05
select months_between('19-12月-1999','19-3月-1999') mon_between from dual; -- 9
SELECT months_between(to_date('2000.05.20','yyyy.mm.dd'),to_date('2005.05.20','yyyy.mm.dd')) mon_betw from dual; -- -60
select to_char(trunc(sysdate,'hh'),'yyyy.mm.dd hh24:mi:ss') hh,
to_char(trunc(sysdate,'mi'),'yyyy.mm.dd hh24:mi:ss') hhmm from dual; -- 2011.01.25 12:00:00 2011.01.25 12:02:00
--(25) ROWIDTOCHAR
select rowid,rowidtochar(rowid),ename from scott.emp;
--(26) TO_CHAR
select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from dual;
--(27) TO_DATE
SELECT TO_DATE('20100101','YYYY/MM/DD') FROM DUAL; --2010-1-1
--(28) TO_NUMBER
select to_number('1999') year from dual;
--(29) GREATEST LEAST
SELECT GREATEST('AA','AB','AC') FROM DUAL; --AC
SELECT LEAST('AA','AB','AC') FROM DUAL; --AA