ORACLE PL/SQL 字符串函数、数学函数、日期函数

-无-为- 2009-08-27 02:52:36



ORACLE PL/SQL 字符串函数、数学函数、日期函数

--【字符串函数】 

--字符串截取substr(字段名，起始点,个数) 

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('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次出现的位置， 

select INSTR('Mississippi','i',5,3) from dual; 

select INSTR('Mississippi','i',-2,3) from dual; 

--返回的是字节 

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,'空') from dual; 

--小写 

select lower('WHo are You') from dual; 

--LPAD左侧用字符串补足到一定长度 

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('WHO......'),'WHO......') from dual; 

--把最左边的字符去掉，使其第一个字符不在其中 

select ltrim('Mississippi','Mis') from dual; 

select ltrim('Mississippi','miD') from dual; 

--RPAD右侧用字符串补足到一定长度 

select RPAD('DFSDf................',9,'WHO') from dual; 

select RPAD('DFSD',9,'WHO') from 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; 

--替换 

select REPLACE('uptown','up','down') from dual; 

--substr和substrb 

select SUBSTR('Message',1,4) from dual; 

select SUBSTR('ABCDEFG',5) from dual; 

select substrb('国际劳动节国营农场',5) from dual; 

select substrb('国际劳动节国营农场',2) from dual; 

select substrb('国际劳动节国营农场',4,8) from dual; 

select substrb('国际劳动节国营农场',3,8) from dual; 

--发音 

select SOUNDEX('dawes') Dawes,SOUNDEX('daws') Daws, SOUNDEX('dawson') from dual; 

--translate 

select TRANSLATE('ABCDefghijklmn','eg','替代') test from dual; 



select TRIM(' Space padded   0..o0..  ') trim from dual; 



select UPPER('abDCCfasdfsdafasdf') from dual; 



--【数学函数】所有函数都有数字参数并返回数字值。所有三角函数的操作数和值都是弧度而不是角度， 

--oracle没有提供内建的弧度和角度的转换函数。 

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 ceil(-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(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.2342342,5),trunc(2.3234234) from dual; 



--【日期函数】操作DATE数据类型，绝大多数都有DATE数据类型的参数 

--增加月 

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; 

--每月最后一天 只区别了日期, 时间没有区别 

select Last_day(sysdate) from dual; 

--如果d1和d2的日的日期都相同，或者都使该月的最后一天，那么将返回一个整数，否则会返回的结果将 



包含一个分数。 

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'),ROUND(sysdate,'dd'), 

        ROUND(sysdate,'HH24'),ROUND(sysDate,'MI') 

from dual; 

select sysdate from dual; 



select trunc(sysdate),trunc(sysdate,'HH24'),trunc(sysdate,'MI') from dual;