Oracle 基礎(chǔ)語句 函數(shù)大全(字符串函數(shù),數(shù)學(xué)函數(shù),日期

1、ORACLE PL/SQL 字符串函數(shù)、數(shù)學(xué)函數(shù)、日期函數(shù)-【字符串函數(shù)】-字符串截取substr(字段名，起始點,個數(shù))select Name,substr(Name,2,4),substr(Name,0,3),substr(Name,-2,3),substr(Name,-2,1) from t1; -字符串從前面取三個(0開始)select Name,substr(Name,0,3) from t1;-字符串從后面取三個select Name,substr(Name,-3,3),length(Name) 串長度 from t1;SELECT ASCII('A'),ASCII

2、('B') from dual;select CHR(100),CHR(80) from dual;select CONCAT(CHR(65),CONCAT(CHR(67),CHR(98) from dual;select CHR(65)|CHR(66)|CHR(76) from dual;-將每個單詞的第一個字母大寫其它字母小寫返回。select INITCAP('substr,abc,substring') from dual;-返回i在MISSISSIPPI中第3次出現(xiàn)的位置，select INSTR('Mississippi','

3、i',5,3) from dual;select INSTR('Mississippi','i',-2,3) from dual;-返回的是字節(jié)select INSTRB('Mississippi','i',5,3) from dual;select INSTRB('Mississippi','i',-2,3) from dual;-長度select length('WHO ARE YOU') from dual;select nvl(null,'空') fr

4、om dual;-小寫select lower('WHo are You') from dual;-LPAD左側(cè)用字符串補足到一定長度select LPAD('DFSDf.',9,'WHO') from dual;select LPAD('DFSD',9,'WHO') from dual;select LPAD('DFSD',9,'') from dual;select LPAD('DFSD',length('DFSD')+length('WH

5、O.'),'WHO.') from dual;-把最左邊的字符去掉，使其第一個字符不在其中select ltrim('Mississippi','Mis') from dual;select ltrim('Mississippi','miD') from dual;-RPAD右側(cè)用字符串補足到一定長度select RPAD('DFSDf.',9,'WHO') from dual;select RPAD('DFSD',9,'WHO') from

6、dual;select RPAD('DFSD',9,'') from dual;select RPAD('DFSD',length('DFSD')+length('WHO.'),'WHO.') from dual;-把最右邊的字符去掉，使其第一個字符不在其中select Rtrim('Mississippi','Mis') from dual;select Rtrim('Mississippi','miD') from dual;-替換

7、select REPLACE('uptown','up','down') from dual;-substr和substrbselect SUBSTR('Message',1,4) from dual;select SUBSTR('ABCDEFG',5) from dual;select substrb('國際勞動節(jié)國營農(nóng)場',5) from dual;select substrb('國際勞動節(jié)國營農(nóng)場',2) from dual;select substrb('國際勞動節(jié)國

8、營農(nóng)場',4,8) from dual;select substrb('國際勞動節(jié)國營農(nóng)場',3,8) from dual;-發(fā)音select SOUNDEX('dawes') Dawes,SOUNDEX('daws') Daws, SOUNDEX('dawson') from dual; -translateselect TRANSLATE('ABCDefghijklmn','eg','替代') test from dual;select TRIM(' Space

9、padded 0.o0. ') trim from dual;select UPPER('abDCCfasdfsdafasdf') from dual;-【數(shù)學(xué)函數(shù)】所有函數(shù)都有數(shù)字參數(shù)并返回數(shù)字值。所有三角函數(shù)的操作數(shù)和值都是弧度而不是角度， -oracle沒有提供內(nèi)建的弧度和角度的轉(zhuǎn)換函數(shù)。select abs(-10.234) from dual;select acos(-1),acos(1) from dual;select asin(1),asin(0) from dual;select atan(1),atan(-1) from dual;select ce

10、il(-2),ceil(5.1) from dual;select cos(-1) from dual;select cosh(1) from dual;select exp(3),exp(2),exp(1) from dual;select Floor(-3.2),floor(5.91) from dual;select LN(2) from dual;select Log(10,2),log(2,10),log(10,1000) from dual;select MOD(19,3),MOD(20,3),MOD(21,3) from dual;select POWER(3,4),POWER(

11、4,2) from dual;select ROUND(12345,-2),ROUND(12345.54321,2),ROUND(-234234.9800234) FROM dual;select SIGN(-10),SIGN(0),SIGN(1000) from dual;select sin(1.57) from dual;select sinh(2.34) from dual;select SQRT(2),sqrt(3) from dual;select TAN(1.2) from dual;select TANH(2.32) from dual;select trunc(2.23423

12、42,5),trunc(2.3234234) from dual;-【日期函數(shù)】操作DATE數(shù)據(jù)類型，絕大多數(shù)都有DATE數(shù)據(jù)類型的參數(shù)-增加月select sysdate,Add_MONTHS(sysdate,1),Add_MONTHS(TO_Date('2008-05-31 18:18:18','yyyy-MM-dd HH24:MI:SS'),1),Add_MONTHS(TO_Date('2008-05-31 18:18:18','yyyy-MM-dd HH24:MI:SS'),2)from dual;-每月最后一天 只區(qū)別了

13、日期, 時間沒有區(qū)別select Last_day(sysdate) from dual;-如果d1和d2的日的日期都相同，或者都使該月的最后一天，那么將返回一個整數(shù)，否則會返回的結(jié)果將 包含一個分?jǐn)?shù)。select MONTHS_BETWEEN(sysdate,To_Date('2008-05-31 14:40:00','yyyy-MM-dd HH24:MI:SS') from dual;select NEW_TIME(sysdate,'GMT','PST') from dual;select NEXT_DAY(To_Date('2007-06-04','yyyy-MM-dd'),'Monday') "1st Monday"from dual;select ROUND(sysDate,'yyyy'),ROUND(sysDate,'mm'