Oracle內置函數

--ASCII碼與字符轉換

select chr(65)||chr(66)||chr(67) ABC,chr(54678) from dual;

select ascii('諙'),ascii('ABC') from dual;

--獲取字符串長度函數

select length('ABCDE我FGHI') from dual;

--字符串截取

select substr('ABCDE我FGHI',5,2),substr('ABCDE我FGHI',-5,2) from dual;

--字符串連接

select concat('Hello',' World!') from dual;

--字符串搜索

select instr('this is a 測試! ','測'),instr('this is a 測試! ','s',-1) from dual;

--字母大小寫轉換

select upper('this is a test') from dual;

select lower('THIS IS A TEST') from dual;

select initcap('this is a test') from dual;

--為指定參數排序函數

select * from productinfo order by nlssort(productname,'NLS_SORT=SCHINESE_PINYIN_M'); --根據productname字段按拼音排序

--替換字符串函數

select replace('this is a test','tes','resul') from dual;

--字符串填充函數

select rpad('test',8,'*rpad'),rpad('test',15,'*rpad'),rpad('test',4,'*rpad') from dual;

--刪除字符串首尾指定字符的函數

select trim(trailing 't' from 'test'),trim(' test    ') from dual;

select rtrim('test    '),rtrim('test*ffs','fs*') from dual;

select ltrim('     ftest','f') from dual;

--字符集名稱和ID互換

select nls_charset_id('US7ASCII') from dual;

select nls_charset_name(1) from dual;

--系統日期、時間函數

select to_char(sysdate,'yyyy-MM-dd hh34:mi:ss') from dual;

select systimestamp from dual;

--得到數據庫時區函數

select dbtimezone from dual;

--為日期加上指定月份函數

select to_char(add_months(to_date('2009-1-30','yyyy-mm-dd'),1),'yyyy-mm-dd') from dual;

--返回當前會話時區

select sessiontimezone from dual;

--返回指定月份最后一天

select last_day(sysdate) from dual;

--返回指定日期后一周的日期

select sysdate,next_day(sysdate,'星期一') from dual;

--返回會話所在時區當前日期

select sessiontimezone,to_char(current_date,'yyyy-mm-dd hh34:mi:ss') from dual;

--提取指定日期特定部分

select extract(year from sysdate) year,

       extract(minute from timestamp '2010-6-18 12:23:10 ') min,

       extract(second from timestamp '2010-6-18 12:23:10 ') sec

  from dual;

--得到兩個日期之間的月份數

select months_between(to_date('2010-7-1', 'yyyy-mm-dd'),

                      to_date('2010-6-1', 'yyyy-mm-dd'))

  from dual;

--時區時間轉換

select to_char(sysdate, 'yyyy-mm-dd hh34:mi:ss') one,

       to_char(new_time(sysdate, 'PDT', 'EST'), 'yyyy-mm-dd hh34:mi:ss') two from dual;

--日期四舍五入、截取函數

select to_char(round(to_date('2010-5-1 21:00:00', 'yyyy-mm-dd hh34:mi:ss')),

               'yyyy-mm-dd hh34:mi:ss')

  from dual;

select to_char(trunc(to_date('2010-5-1 09:00:00', 'yyyy-mm-dd hh34:mi:ss')),

               'yyyy-mm-dd hh34:mi:ss')

  from dual;

--字符串轉ASCII類型字符串

select asciistr('這是測試!') from dual;

--二進制轉十進制

select bin_to_num(1),bin_to_num(1,0,0),bin_to_num(1,1,1) from dual;

--數據類型轉換函數

select cast('123' as integer) as vhr,

       cast(123 as varchar2(8)) as num,

       cast(sysdate as varchar2(12)) as dt

  from dual;

--字符串和rowid相互轉換

select chartorowid('AAARXnAABAAAVgggAB') from dual;

select rowidtochar('AAARXnAABAAAVggAAB') from dual;

--字符串在字條集間轉換

select convert('測試','US7ASCII') from dual;

--十六進制字符串與RAW類型相互轉換

select hextoraw('4d') from dual;

select rawtohex('4D') from dual;

select rawtonhex('4D') from dual;

--數值轉換成字符

select to_char(16.89,'99.9'),to_char(16.89) from dual;

select to_char(sysdate, 'yyyy-mm-dd'),

       to_char(sysdate, 'hh34:mi:ss'),

       to_char(sysdate, 'month', 'NLS_DATE_LANGUAGE=ENGLISH')

  from dual;

--字符轉日期

select to_char(to_date('2010-7-1','yyyy-mm-dd'),'month') from dual;

select to_char(to_date('2010-7-1','yyyy-mm-dd'),'yyyy-mm-dd') from dual;

--字符串轉數據

select to_number('2456.304','9999.999') from dual;

--全角轉半角

select to_single_byte('Ｔｈｉｓ ｉｓ ａ Ｔｅｓｔ') from dual;

--返回表達式為NULL的函數

select coalesce(null,9-9,null) from dual;

--排除指定條件函數

select * from productinfo where lnnvl(quantity>70);

--替換NULL值函數

select nvl(null,0) from dual;

select nvl2('true',1,3) from dual;

--求平均值函數

select avg(productprice) from productinfo group by category;

--求記錄量

select count(*) from productinfo;

--最大、最小值函數

select * from productinfo where productprice=(select max(productprice) from productinfo);

--求和函數

select sum(all quantity),category from productinfo group by category;

--返回登錄名

select user from dual;

--返回會話以及上下文信息

select userenv('isdba') from dual;

select sys_context('userenv','session_user') session_user from dual;

--表達式匹配函數

select productname,quantity,decode(sign(quantity-80),1,'充足',-1,'不足',0,'不足') from productinfo;