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 hh24: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 hh24: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 hh24:mi:ss‘) one,

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

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

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

               ‘yyyy-mm-dd hh24:mi:ss‘)

  from dual;

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

               ‘yyyy-mm-dd hh24: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, ‘hh24: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;

本文出自 “J风知劲草” 博客，请务必保留此出处http://gifilee.blog.51cto.com/8930516/1630795