|
1.字符函數(shù) ascii('字符串') \ chr(ASCII碼) 返回第一個(gè)字母的ASCII碼\函數(shù)返回十進(jìn)制表示的字符 $ `" W. \2 g2 y3 X% G( v select ascii('Android') big_A,ascii('android') small_a from dual; //65 97 select chr(65) big_A,chr(97) small_a from dual; //A a concat(string1,string2) 將string2連續(xù)到string1的后面 select concat('oracle','SQL') name from dual; //oracleSQL initcap(string) 將每個(gè)單詞的第一個(gè)字母大寫其他字母小寫返回 1 p3 P6 E. P2 G' |0 M3 c+ O select initcap('anroid,java,c#') ceasar from dual; //Anroid,Java,C# 1 p3 P6 E. P2 G' |0 M3 c+ upper(string)/lower(string) select upper('hi') from dual; //HI select lower('HI') from dual; //hi instr(string1,string2,index_postion,num) 返回string2在string1中第num次出現(xiàn)的位置,搜索從string1的第index_postion個(gè)字符開始,無(wú)則返回0 (從左到右第三個(gè)位置開始查找Mississippi中第三次出現(xiàn)i的位置) select instr('Mississippi','i',3,3) from dual;//11 (從右到左第三個(gè)位置開始查找Mississippi中第三次出現(xiàn)i的位置) select instr('Mississippi','i',-3,3) from dual;//2 length(string) 返回string的長(zhǎng)度 select length('android java') ergo from dual;//12 lower(string) 返回string的小寫狀態(tài) select lower('ANDROID JAVA') ergo from dual;//android java Lpad(string,length,append) Rpad(string,length,append) 將append補(bǔ)至string前滿足最大長(zhǎng)度為length;若最大長(zhǎng)度<string則從左向右截取字符串 將append補(bǔ)至string后滿足最大長(zhǎng)度為length;若最大長(zhǎng)度<string則從左向右截取字符串 select Lpad('tech', 8, '0') padded from dual;//0000tech select Rpad('tech', 8, '0') padded from dual;//tech0000 select Lpad('tech', 2) padded from dual;//te select Rpad('tech', 2) padded from dual;//ch Ltrim(string,pattern) Rtrim(string,pattern) 從最左邊的字符開始,使其第一個(gè)字符不在pattern中,如果沒有pattern,那么string就不會(huì)改變 + g3 g- [, j6 i( y6 v1 t8 `) H 從最右邊的字符開始,使其第一個(gè)字符不在pattern中,如果沒有pattern,那么string就不會(huì)改變 + g3 g- [, j6 i( y6 v1 t8 `) H select Ltrim('Mississippi','Mis')from dual;//ippi select Ltrim('MTississippi','Mis')from dual;//Tississippi select Rtrim('Mississippi','Mis')from dual;//Mississipp select Rtrim('MTississippi','pis')from dual;//MT replace(string1,string2,string3) 用string3代替出現(xiàn)在string1中的string2 select replace('uptown','up','down') from dual;//downtown substr(string1,index,end) select substr('Message',1,4) from dual;//Mess translate(string,str1,str2) 將string中與str1相同的字符以str2代替 ! n0 D" h. L* }6 X' \7 f/ select translate('fumble','uf','ar') test from dual;//ramble trim(pattern,string) 從字符串的頭部,尾部或兩端截?cái)嗵囟ㄗ址?/b> select trim(' ' from ' AB C D EF ') from dual;//AB C D EF 2.數(shù)學(xué)函數(shù) abs(數(shù)字字符/數(shù)字) exp(數(shù)字字符/數(shù)字) floor(數(shù)字字符/數(shù)字) mod(數(shù)字1，數(shù)字2) power(數(shù)字,次方) round(數(shù)字1，四舍五入保留位數(shù)) sqrt(數(shù)字) 返回絕對(duì)值 返回e的n次冪,e=2.71828183 , R& K8 N; y2 J; X. p l 返回小于等于其的最小整數(shù) l 返回?cái)?shù)字1%數(shù)字2 返回?cái)?shù)字的次方 返回四舍五入后數(shù)字 返回?cái)?shù)字的平方根 select abs('-23') from dual; //23 select floor(-16.35) from dual;//-17 select mod(18,4) from dual; //2 select power(2,3) from dual; //8 select round(12345.54621,2) from dual;//12345.55 select sqrt(8) from dual; //2.82842712474619009760337744841939615714 trunc(日期或數(shù)字) /**************日期********************/ 1 select to_char(trunc(sysdate),'yyyy-MM-dd') from dual //2014-03-18 返回當(dāng)前年月日 select to_char(trunc(sysdate,'dd'),'yyyy-MM-dd') from dual //2014-03-18 返回當(dāng)前年月日 2.select to_char(trunc(sysdate,'mm'),'yyyy-MM-dd') from dual //2014-03-01 返回當(dāng)月第一天. 3.select to_char(trunc(sysdate,'yy'),'yyyy-MM-dd') from dual // 2014-01-01 返回當(dāng)年第一天 select to_char(trunc(sysdate,'yyyy'),'yyyy-MM-dd') from dual // 2014-01-01 返回當(dāng)年第一天 4.select to_char(trunc(sysdate,'d'),'yyyy-MM-dd') from dual //2014-03-16 返回當(dāng)前星期的第一天 5.select to_char(trunc(sysdate,'hh'),'yyyy-MM-dd HH:mi:ss') from dual //2014-03-16 09:00:00 /***************數(shù)字********************/ 6. select trunc(123.458) from dual //123 7. select trunc(123.458,0) from dual //123 8. select trunc(123.458,1) from dual //123.4 9. select trunc(123.458,-1) from dual //120 10 select trunc(123.458,-4) from dual //0 11.select trunc(123.458,4) from dual //123.458 12.select trunc(123) from dual //123 13.select trunc(123,1) from dual //123 14.select trunc(123,-1) from dual //120 |
|
|
來(lái)自： 昵稱16088576 > 《SQL筆記》
