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;