導讀:1建站知識ORACLE 常用函數(shù)總結(80個),大家可以參考下。建設網(wǎng)站網(wǎng)站建設制作。

1. ASCII 返回與指定的字符對應的十進制數(shù); SQL> select ascii(A) A,ascii(a) a,ascii(0) zero,ascii( ) space from dual; A A ZERO SPACE --------- --------- --------- --------- 65 97 48 32 2. CHR 給出整數(shù),返回對應的字符; SQL> select chr(54740) zhao,chr(65) chr65 from dual; ZH C -- - 趙 A 3. CONCAT 連接兩個字符串; SQL> select concat(010-,88888888)||轉23 高乾競電話 from dual; 高乾競電話 ---------------- 010-88888888轉23 4. INITCAP 返回字符串并將字符串的第一個字母變?yōu)榇髮? SQL> select initcap(smith) upp from dual; UPP ----- Smith 5.INSTR(C1,C2,I,J) 在一個字符串中搜索指定的字符,返回發(fā)現(xiàn)指定的字符的位置; C1 被搜索的字符串 C2 希望搜索的字符串 I 搜索的開始位置,默認為1 J 出現(xiàn)的位置,默認為1 SQL> select instr(oracle traning,ra,1,2) instring from dual; INSTRING --------- 9 6.LENGTH 返回字符串的長度; SQL> select name,length(name),addr,length(addr),sal,length(to_char(sal)) from gao.nchar_tst; NAME LENGTH(NAME) ADDR LENGTH(ADDR) SAL LENGTH(TO_CHAR(SAL)) ------ ------------ ---------------- ------------ --------- -------------------- 高乾競 3 北京市海錠區(qū) 6 9999.99 7 7.LOWER 返回字符串,并將所有的字符小寫 SQL> select lower(AaBbCcDd)AaBbCcDd from dual; AABBCCDD -------- aabbccdd 8.UPPER 返回字符串,并將所有的字符大寫 SQL> select upper(AaBbCcDd) upper from dual; UPPER -------- AABBCCDD 9.RPAD和LPAD(粘貼字符) RPAD 在列的右邊粘貼字符 LPAD 在列的左邊粘貼字符 SQL> select lpad(rpad(gao,10,*),17,*)from dual; LPAD(RPAD(GAO,1 ----------------- *******gao******* 不夠字符則用*來填滿 10.LTRIM和RTRIM LTRIM 刪除左邊出現(xiàn)的字符串 RTRIM 刪除右邊出現(xiàn)的字符串 SQL> select ltrim(rtrim( gao qian jing , ), ) from dual; LTRIM(RTRIM( ------------- gao qian jing 11.SUBSTR(string,start,count) 取子字符串,從start開始,取count個 SQL> select substr(13088888888,3,8) from dual; SUBSTR( -------- 08888888 12.REPLACE(string,s1,s2) string 希望被替換的字符或變量 s1 被替換的字符串 s2 要替換的建設網(wǎng)站公司字符串 SQL> select replace(he love you,he,i) from dual; REPLACE(H ---------- i love you 13.SOUNDEX 返回一個與給定的字符串讀音相同的字符串 SQL> create table table1(xm varchar(8)); SQL> insert into table1 values(weather); SQL> insert into table1 values(wether); SQL> insert into table1 values(gao); SQL> select xm from table1 where soundex(xm)=soundex(weather); XM -------- weather wether ? 14.TRIM(s from string) LEADING 剪掉前面的字符 TRAILING 剪掉后面的字符 如果不指定,默認為空格符 15.ABS 返回指定值的絕對值 SQL> select abs(100),abs(-100) from dual; ABS(100) ABS(-100) --------- --------- 100 100 19.CEIL 返回大于或等于給出數(shù)字的最小整數(shù) SQL> select ceil(3.1415927) from dual; CEIL(3.1415927) --------------- 4 22.EXP 返回一個數(shù)字e的n次方根 SQL> select exp(2),exp(1) from dual; EXP(2) EXP(1) --------- --------- 7.3890561 2.7182818 23.FLOOR 對給定的數(shù)字取整數(shù) SQL> select floor(2345.67) from dual; FLOOR(2345.67) -------------- 2345 26.MOD(n1,n2) 返回一個n1除以n2的余數(shù) SQL> select mod(10,3),mod(3,3),mod(2,3) from dual; MOD(10,3) MOD(3,3) MOD(2,3) --------- --------- --------- 1 0 2 27.POWER 返回n1的n2次方根 SQL> select power(2,10),power(3,3) from dual; POWER(2,10) POWER(3,3) ----------- ---------- 1024 27 28.ROUND和TRUNC 按照指定的精度進行舍入 SQL> select round(55.5),round(-55.4),trunc(55.5),trunc(-55.5) from dual; ROUND(55.5) ROUND(-55.4) TRUNC(55.5) TRUNC(-55.5) ----------- ------------ ----------- ------------ 56 -55 55 -55 29.SIGN 取數(shù)字n的符號,大于0返回1,小于0返回-1,等于0返回0 SQL> select sign(123),sign(-100),sign(營銷型網(wǎng)站建設0) from dual; SIGN(123) SIGN(-100) SIGN(0) --------- ---------- --------- 1 -1 0 36.ADD_MONTHS 增加或減去月份 SQL> select to_char(add_months(to_date(199912,yyyymm),2),yyyymm) from dual; TO_CHA ------ 200002 SQL> select to_char(add_months(to_date(199912,yyyymm),-2),yyyymm) from dual; TO_CHA ------ 199910 37.LAST_DAY 返回日期的最后一天 SQL> select to_char(sysdate,yyyy.mm.dd),to_char((sysdate)+1,yyyy.mm.dd) from dual; TO_CHAR(SY TO_CHAR((S ---------- ---------- 2004.05.09 2004.05.10 SQL> select last_day(sysdate) from dual; LAST_DAY(S ---------- 31-5月 -04 38.MONTHS_BETWEEN(date2,date1) 給出date2-date1的月份 SQL> select months_between(19-12月-1999,19-3月-1999) mon_between from dual; MON_BETWEEN ----------- 9 SQL>selectmonths_between(to_date(2000.05.20,yyyy.mm.dd),to_date(2005.05.20,yyyy.mm.dd)) mon_betw from dual; MON_BETW --------- -60 39.NEW_TIME(date,this,that) 給出在this時區(qū)=other時區(qū)的日期和時間 SQL> select to_char(sysdate,yyyy.mm.dd hh24:mi:ss) bj_time,to_char(new_time 2 (sysdate,PDT,GMT),yyyy.mm.dd hh24:mi:ss) los_angles from dual; BJ_TIME LOS_ANGLES ------------------- ------------------- 2004.05.09 11:05:32 2004.05.09 18:05:32 40.NEXT_DAY(date,day) 給出日期date和星期x之后計算下一個星期的日期 SQL> select next_day('18-5月-2001','星期五') next_day from dual; NEXT_DAY ---------- 25-5月 -01 41.SYSDATE 用來得到系統(tǒng)的當前日期 SQL> select to_char(sysdate,dd-mm-yyyy day) from dual; TO_CHAR(SYSDATE, ----------------- 09-05-2004 星期日 trunc(date,fmt)按照給出的要求將日期截斷,如果fmt=mi表示保留分,截斷秒 SQL> select to_char(trunc(sysdate,hh),yyyy.mm.dd hh24:mi:ss) hh, 2 to_char(trunc(sysdate,mi),yyyy.mm.dd hh24:mi:ss) hhmm from dual; HH HHMM ------------------- ------------------- 2004.05.09 11:00:00 2004.05.09 11:17:00 42.CHARTOROWI網(wǎng)站seo優(yōu)化課程D 將字符數(shù)據(jù)類型轉換為ROWID類型 SQL> select rowid,rowidtochar(rowid),ename from scott.emp; ROWID ROWIDTOCHAR(ROWID) ENAME ------------------ ------------------ ---------- AAAAfKAACAAAAEqAAA AAAAfKAACAAAAEqAAA SMITH AAAAfKAACAAAAEqAAB AAAAfKAACAAAAEqAAB ALLEN AAAAfKAACAAAAEqAAC AAAAfKAACAAAAEqAAC WARD AAAAfKAACAAAAEqAAD AAAAfKAACAAAAEqAAD JONES 43.CONVERT(c,dset,sset) 將源字符串 sset從一個語言字符集轉換到另一個目的dset字符集 SQL> select convert(strutz,we8hp,f7dec) "conversion" from dual; conver ------ strutz相關建設網(wǎng)站網(wǎng)站建設制作。

關鍵詞標簽: 函數(shù) 常用