oracle较常用函数整理

select abs (-100) from dual;--绝对值

select mod (8,5) from dual;--取模,取余数

select ceil (12.1) from dual;--去上限值

select floor (12.1) from dual;--去下限值

select round (12.4567,3) from dual; -- 四舍五入

select trunc (12.456,2) from dual; -- 截取，不四舍五入

select trunc (12.456,0) from dual; -- 截取，不四舍五入(截整)

select length ('asddsa') from dual;--字符串长度

select xingm, length (xingm) from t_hq_ryxx;

select xingm, substr (xingm,1,2) from t_hq_ryxx;--截取，（从第一位开始截取，截两位）

select concat ('sa',concat ('sdsd','ddd')) from dual;

select 'sa'||'sdsd'||'ddd' from dual;

--查找字符串

select instr ('abcdef','d') from dual;

select instr ('abcdefdf','d',3) from dual;

select instr ('abcdefdf','d',5) from dual; --数字指定起始位置

select instr ('abcdefdf','dd',3) from dual; --找不到返回0

--转换大小写

select upper ('assa'),lower ('SDDA') from dual;

select upper ('assa'),lower ('SDDA'), initcap ('this is a car') from dual; --initcap 首字母转换大写

select replace ('abcdef','ab','123') from dual; --替换

update t_hq_ryxx set xingm = replace (xingm,'三','四') where xingm like '三%'

--填充
select rpad ('aa',8, 'c') from dual;

select rpad ('aba',8, 'de') from dual;

select lpad ('aa',8, 'rc') from dual;

--去空格

select trim (' wfat ') from dual; --去前后空格

select ltrim (' sd1 ') from dual; --去左空格

select rtrim (' sdad ') from dual; --去右空格

--去前缀

select trim (leading 'a' from 'asda') from dual; --前边开始

select trim (trailing 'a' from 'asda') from dual; --右边开始

select trim (both 'a' from 'asda') from dual; --去前后

--日期型函数

select sysdate from dual;

select add_months(sysdate,2) from dual; --加两个月

select add_months(sysdate,-2) from dual; --减两个月

select last_day(sysdate) from dual;

select last_day(sysdate) +1 from dual; --（+）加天数

select last_day(sysdate) -1 from dual; --（-）减天数

--转换函数

--select cast ('123' as number) from dual;

--select cast ('123' as number) + 123 from dual;

--select cast ('123' as varchar2(4)) from dual; --数字长度不能超过字符串长度

--select cast(sysdate as varchar2(10)) from dual;

select to_char(sysdate, 'YYYY-MM-DD')from dual; --日期转换字符串(忽略大小写)

select to_char(sysdate, 'yy-mm-dd')from dual;

select to_char(sysdate, 'YYYY-DD-MM HH24:mi:ss')from dual;

--select to_char(123.456,'9999.9')from dual;

select to_date('2015-12-11','yyyy-mm-dd') from dual; --字符串转换日期

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

select( nianl + gongz) from t_hq_ryxx;