常常要用到一些函數還是做什麼計算的時候
有時知道是哪一個可是會忘記對不對或是如何使用
放一份自己找的到的很快就可以查到也不錯.....
--1.ASCII=>返回與指定的字符對應的十進制數
select ascii('A') A, ascii('a') a, ascii('0') zero, ascii(' ') space, ascii('趙') Word from dual
--結果：
--A A ZERO SPACE WORD
--65 97 48 32 48047
--2.CHR=>給出整數,返回對應的字符;
select chr(48047) zhao, chr(65) chr65 from dual
--結果：
-- ZHAO CHR65
-- 趙 A
--3.CONCAT=>連接兩個字串;
select concat('0000-','111111')||'轉33663' As PhoneNo from dual
--結果：
-- PHONENO
-- 0000-111111轉33663
--4.INITCAP=>返回字串並將字串的第一個字母變為大寫;
select initcap('simle to me') FirstWordToupper from dual
--結果
-- FIRSTWORDTOUPPER
-- Simle To Me
--5.INSTR(S1,S2,B,E)
--在一個字串中尋找指定的字串,回傳指定的字串位置;
--S1 被搜尋字串 S2 要搜尋字串 B 開始位置,預設為1 E 結束位置,預設為1
select instr('oracle traning','ra',1,2) instring from dual
--結果
-- INSTRING
-- 9
--6.LENGTH=>回傳字串長度
select '我是誰' As CName, length('我是誰') AS CLength,
'WHO AND I' As EName, length('WHO AND I') As ELength,
'99999.99' As iNum,length(to_char(99999.99)) As iLength
from dual
--結果
-- CNAME |CLENGTH |ENAME |ELENGTH |INUM |ILENGTH
-- 我是誰 |3 |WHO AND I |9 |99999.99 |8
--7.LOWER=>將所有字串轉為小寫
select lower('AaBbCcDd') As strResult from dual
--結果
-- STRRESULT
-- aabbccdd
--8.UPPER=>將所有字串轉為大寫
select upper('AaBbCcDd') As strResult from dual
--結果
-- STRRESULT
-- AABBCCDD
--9.RPAD和LPAD
--RPAD 在右邊補足設定長度或是截掉多於設定長度字串
--LPAD 在左邊補足設定長度或是截掉多於設定長度字串
select rpad('web',1,'~') as strResult from dual
select rpad('web',5,'~') as strResult from dual
select lpad('web',1,'~') as strResult from dual
select lpad('web',5,'~') as strResult from dual
select lpad(rpad('test',11,'~'),18,'~')from dual
--結果
--strResult
--w
--web~~
--w
--~~web
--10.LTRIM和RTRIM
--LTRIM 由左至右清除與設定相同的內容Oracle是一個一個比對有出現就清除至找不到為止之後出現也不會清除
--RTRIM 由右至左清除與設定相同的內容Oracle是一個一個比對有出現就清除至找不到為止之後出現也不會清除
select ltrim('902100111000991110224323','1') from dual
--結果
--902100111000991110224323
select ltrim('90100111000991110224323','109') from dual
--結果
--224323
--------------------------------------------------------------
--11.SUBSTR(string,start,count)取字串,從start開始,取count
select substr('123456789',3,2) As sSubstr from dual
--結果
--sSubstr
--34
--12.REPLACE('string','s1','s2')
--string 被取代的字串或變數 s1 被取代的字串 s2 要取代的字串
select replace('This is test word','test','prod') As sResult from dual
--結果
--sResult
--This is prod word
--13.SOUNDEX=>回傳與參數的字串讀音相同的字串
--下列三者讀音都相同所以回傳的值都一樣
select soundex('two') As t1, soundex('too') As t2, soundex('to') Ast3 from dual
select soundex('cap') As c1,soundex('cup') As c2 from dual
--結果
-- t1 t2 t3
-- T000 T000 T000
-- c1 c2
-- C100 C100
--14.TRIM去除空白或指定字元
select trim(' as ') As t1 from dual
select ltrim('@as','@') As t1 from dual
select ltrim(' as') As t1 from dual
select rtrim('as@','@') As t1 from dual
select rtrim('as ') As t1 from dual
--結果
--t1
--as
--as
--as
--as
--15.ABS 回傳指定值的絕對值
select abs(100) AS a1, abs(-100)As a2 from dual
--結果
-- a1 a2
-- 100 100
--16.ACOS=>反餘弦的值(oracle沒有提供內建的弧度和角度的轉換函數)
select ACOS(-1) pi,ACOS(1) ZERO FROM dual
--結果
--pi ZERO
--3.1415927 0
--17.ASIN=>反正玄函數，返回-1到1，弧度
select asin(0.5),asin(1),asin(-1) from dual
--結果
--ASIN(.5) ASIN(1) ASIN(-1)
--0.523598775598299 1.5707963267949 -1.5707963267949
--18.ATAN=>反正切函數
select atan(1) from dual
--結果
--ATAN(1)
--0.78539816
--19.CEIL=>返回大於或等於輸入數字的最小整數
select ceil(93.1541) from dual
--結果
--CEIL(93.1541)
--94
--20.COS=>返回輸入數字的餘弦
select cos(-3.1415927) from dual
-- 結果
-- COS(-3.1415927)
-- -1
--------------------------------------------------------------
--21.COSH=>返回輸入數字反餘弦值 select cosh(20) from dual --結果 --COSH(20) --242582597.704895 --22.EXP=>返回輸入數字e的n次方根 select exp(2),exp(1) from dual --結果 --EXP(2) EXP(1) --7.3890561 2.7182818 --23.FLOOR=>輸入數字取整數 select floor(2345.67) from dual; --結果 --FLOOR(2345.67) --2345 --24.LN=>返回輸入數字的自然對數 select ln(1),ln(2),ln(2.7182818) from dual --結果 --LN(1) LN(2) LN(2.7182818) --0 0.693147180559945 0.999999989530502 --25.LOG(n1,n2)=>返回以n1為底n2的對數 select log(2,1),log(2,4) from dual --結果 --LOG(2,1) LOG(2,4) --0 2 --26.MOD(n1,n2)=>返回n1除以n2的餘數 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次方根 select power(2,15),power(3,3) from dual --結果 --POWER(2,50) POWER(3,3) --32768 27 --28.ROUND和TRUNC=>按照指定的精度進行捨入 select round(55.5),round(-55.4),trunc(55.5),trunc(-55.5) from dual select ROUND(12355,-2),ROUND(12345.54521,2), trunc(12355,-2),trunc(12345.54521,2) FROM dual --結果 --ROUND(55.5) ROUND(-55.4) TRUNC(55.5) TRUNC(-55.5) --56 -55 55 -55 --12400 12345.55 12300 12345.54 --29.SIGN=>取數字n的符號,大於0返回1,小於0返回-1,等於0返回0 select sign(123),sign(-100),sign(0) from dual --結果 --SIGN(123) SIGN(-100) SIGN(0) --1 -1 0 --30.SIN=>返回輸入數字的正弦值 select sin(1.57079) from dual --結果 --SIN(1.57079) --1
--------------------------------------------------------------
--31.SIGH=>返回雙曲正弦的值
select sin(20),sinh(20) from dual
--結果
--SIN(20) SINH(20)
--.91294525 242582598
--32.SQRT=>返回數字n的根
select sqrt(64),sqrt(10) from dual
--結果
--SQRT(64) SQRT(10)
-- 8 3.1622777
--33.TAN=>返回數字的正切值
select tan(20),tan(10) from dual
--結果
--TAN(20) TAN(10)
--2.2371609 .64836083
--34.TANH=>返回數字n的雙曲正切值
select tanh(20),tan(20) from dual
--結果
--TANH(20) TAN(20)
1 2.2371609
--35.TRUNC=>按照指定的精度截取一個數
select trunc(124.1666,-2) trunc1,trunc(124.16666,2) from dual
--結果
--TRUNC1 TRUNC(124.16666,2)
-- 100 124.16
--36.ADD_MONTHS=>增加或減去月份
select to_char(add_months(to_date('201006','yyyymm'),2),'yyyymm') from dual
--TO_CHAR
--201008
select to_char(add_months(to_date('201006','yyyymm'),-2),'yyyymm') from dual
--TO_CHA
--201004
--37.LAST_DAY=>返回日期的最後一天
select last_day(sysdate) strLastDate from dual
--結果
--strLastDate
--2010/7/31 下午 02:48:23
--38.MONTHS_BETWEEN(date2,date1)=>date2-date1的月份
select months_between('06-7月-2010','06-5月-2010') mon_between from dual
--結果
--MON_BETWEEN
-- 2
select months_between(to_date('2009.05.20','yyyy.mm.dd'),to_date('2010.05.20','yyyy.mm.dd')) mon_betw from dual
--結果
--MON_BETW
-- -12
--39.NEW_TIME(date,'this','that')=>在this時區=other時區的日期和時間
select to_char(sysdate,'yyyy.mm.dd hh24:mi:ss') bj_time,to_char(new_time
(sysdate,'PDT','GMT'),'yyyy.mm.dd hh24:mi:ss') los_angles from dual
--結果
--BJ_TIME LOS_ANGLES
--2010.07.06 14:53:32 2010.07.06 21:53:32
--40.NEXT_DAY(date,'day')=>日期date和星期x之後計算下一個星期x的日期
select next_day('6-7月-2010','星期二') next_day from dual
--結果
--NEXT_DAY
--2010/7/13
--------------------------------------------------------------
--41.SYSDATE=>用來得到系統的當前日期及星期x
select to_char(sysdate,'dd-mm-yyyy day') strToday from dual
--結果
--strToday
--06-07-2010 星期二
--42.trunc(date,fmt) 按照給出的要求將日期截斷,如果fmt='mi'表示保留分,截斷秒
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
--結果
--HH HHMM
--2010.07.06 14:00:00 2010.07.06 14:58:00
--43.CHARTOROWID=>將字串類型轉換為ROWID類型
select rowid,rowidtochar(rowid),ENAME from usertable
--結果
--ROWID ROWIDTOCHAR(ROWID) ENAME
--AAAAfKAACAAAAEqAAA AAAAfKAACAAAAEqAAA SMITH
--AAAAfKAACAAAAEqAAB AAAAfKAACAAAAEqAAB ALLEN
--44.CONVERT(c,dset,sset)=>將原字串 sset從一個語言字符集轉換到另一個目的dset字符集
select convert('strutz','we8hp','f7dec') "conversion" from dual
--結果
--conversion
--strutz
--45.HEXTORAW=>將一個十六進位構成的字串轉換為二進位
--46.RAWTOHEXT=>將一個二進餒構成的字串轉換為十六進位
--47.ROWIDTOCHAR=>將ROWID數據類型轉換為字符類型
--48.TO_CHAR(date,'format')=>將日期轉為字串
select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') strTOCHAR from dual
--結果
--strTOCHAR
--2010/07/06 15:11:27
--49.TO_DATE(string,'format')將字串轉為日期
select to_date('20100706','YYYYMMDD') strTODATE from dual
--結果
--strTODATE
--2010/7/6
--50.TO_MULTI_BYTE=>將字串中的單字節字轉化為多字節字
select to_multi_byte('高') As tomultibyte from dual
--結果
--tomultibyte
--高
--------------------------------------------------------------
--51.TO_NUMBER=>將輸入的字串轉換為數字
select to_number('1999') year from dual
--結果
--year
--1999
--52.BFILENAME(dir,file)=>指定一個外部二進制文件
insert into test_tbl values(bfilename('lob_dir1','image1.gif'))
--53.CONVERT('x','desc','source')=>將x字段或變量的source轉換為desc
SELECT CONVERT('GroB','US7ASCII','WE8HP') FROM dual
--54.DUMP(s,fmt,start,length)、EMPTY_BLOB()、EMPTY_CLOB()
--PS：沒用到不會解釋
--55.GREATEST=>返回一組表達式中的最大值,比較字串的編碼大小.
select greatest('AA','AB','AC') from dual
--結果
--GR
--AC
select greatest('啊','安','天') from dual
--結果
--GR
--啊
--56.LEAST=>返回一組表達式中的最小值
select least('啊','安','天') from dual
--結果
--LE
--天
--57.USER=>返回目前用戶的名字
select user from dual
--結果
--USER
--xxxx
--58.USEREVN=>返回當前用戶環境的信息,opt可以是:
--ENTRYID,SESSIONID,TERMINAL,ISDBA,LABLE,LANGUAGE,CLIENT_INFO,LANG,VSIZE
--ISDBA 查看當前用戶是否是DBA如果是則返回true
SQL> select userenv('isdba') from dual;
--結果
--USEREN
--FALSE
--59.AVG(DISTINCT|ALL)=>all表示對所有的值求平均值,distinct只對不同的值求平均值
select avg(distinct img11) from img_file
--結果
--AVG(DISTINCTSAL)
--27657.5592840114
select avg(all img11) from img_file
--結果
--AVG(ALLSAL)
--4540.0510831344
--60.MAX(DISTINCT|ALL)=>求最大值,ALL表示對所有的值求最大值,DISTINCT 表示對不同的值求最大值,相同的只取一次
select MAX(distinct img11) from img_file
--MAX(DISTINCTSAL)
-- 4800000
--------------------------------------------------------------
--61.MIN(DISTINCT|ALL)=>求最小值,ALL表示對所有的值求最小值,DISTINCT表示對不同的值求最小值,相同的只取一次
select MIN(distinct img11) from img_file
--結果
--MIN(ALLSAL)
-- -18106
--62.STDDEV(distinct|all)=>求標準差,ALL表示對所有的值求標準差,DISTINCT表示只對不同的值求標準差
select stddev(img11) from img_file
--結果
-- STDDEV(SAL)
-- 23598.901717834
--63.VARIANCE(DISTINCT|ALL)=>求協方差
select variance(img11) from img_file
--結果
--VARIANCE(SAL)
--556908162.287986
--64.NVL(expr1, expr2) 拿expr1來判斷，是null就用expr2取代
--65.NVL2(expr1, expr2, expr3) 拿expr1判斷，不是null填expr2，是null填expr3
--66.NULLIF(expr1, expr2) 如果1=2就塞null，不相等就填1
--67.COALESCE (expr1， expr2,…,exprn) 從1開始找，不是null就回傳該值
--68.Decode(條件,值1,回傳值1,值2,回傳值2,...值n,回傳值n,ELSE回傳值)
select decode(10,10,1,2) As strResult from dual
--結果
--1
