Oracle函数整理

  1 select abs (-100) from dual;--绝对值
  2 
  3 select mod (8,5) from dual;--取模,取余数
  4 
  5 select ceil (12.1) from dual;--去上限值
  6 
  7 select floor (12.1) from dual;--去下限值
  8 
  9 select round (12.4567,3) from dual; -- 四舍五入
 10 
 11 select trunc (12.456,2) from dual; -- 截取，不四舍五入
 12 
 13 select trunc (12.456,0) from dual; -- 截取，不四舍五入(截整)
 14 
 15 select length (‘asddsa‘) from dual;--字符串长度
 16 
 17 select xingm, length (xingm) from t_hq_ryxx;
 18 
 19 select xingm, substr (xingm,1,2) from t_hq_ryxx;--截取，（从第一位开始截取，截两位）
 20 
 21 select concat (‘sa‘,concat (‘sdsd‘,‘ddd‘)) from dual;
 22 
 23 select ‘sa‘||‘sdsd‘||‘ddd‘ from dual;
 24 
 25 --查找字符串
 26 
 27 select instr (‘abcdef‘,‘d‘) from dual;
 28 
 29 select instr (‘abcdefdf‘,‘d‘,3) from dual;
 30 
 31 select instr (‘abcdefdf‘,‘d‘,5) from dual; --数字指定起始位置
 32 
 33 select instr (‘abcdefdf‘,‘dd‘,3) from dual; --找不到返回0
 34 
 35 --转换大小写
 36 
 37 select upper (‘assa‘),lower (‘SDDA‘) from dual;
 38  
 39 select upper (‘assa‘),lower (‘SDDA‘), initcap (‘this is a car‘) from dual;  --initcap 首字母转换大写
 40 
 41 select replace (‘abcdef‘,‘ab‘,‘123‘) from dual;    --替换
 42 
 43 update t_hq_ryxx set xingm = replace (xingm,‘三‘,‘四‘) where xingm like ‘三%‘
 44 
 45  --填充
 46 select rpad (‘aa‘,8, ‘c‘) from dual;
 47 
 48 select rpad (‘aba‘,8, ‘de‘) from dual;
 49 
 50 select lpad (‘aa‘,8, ‘rc‘) from dual;
 51 
 52 --去空格
 53 
 54 select trim (‘ wfat ‘) from dual; --去前后空格
 55 
 56 select ltrim (‘ sd1 ‘) from dual; --去左空格
 57 
 58 select rtrim (‘ sdad ‘) from dual; --去右空格
 59 
 60 --去前缀
 61 
 62 select trim (leading ‘a‘ from ‘asda‘) from dual; --前边开始
 63 
 64 select trim (trailing ‘a‘ from ‘asda‘) from dual; --右边开始
 65 
 66 select trim (both ‘a‘ from ‘asda‘) from dual; --去前后
 67 
 68 
 69 --日期型函数
 70 
 71 select sysdate from dual;
 72 
 73 select add_months(sysdate,2) from dual;  --加两个月
 74 
 75 select add_months(sysdate,-2) from dual; --减两个月
 76 
 77 select last_day(sysdate) from dual;
 78 
 79 select last_day(sysdate) +1 from dual; --（+）加天数
 80 
 81 select last_day(sysdate) -1 from dual; --（-）减天数
 82 
 83 --转换函数
 84 
 85 --select cast (‘123‘ as number) from dual;
 86 
 87 --select cast (‘123‘ as number) + 123 from dual;
 88 
 89 --select cast (‘123‘ as varchar2(4)) from dual; --数字长度不能超过字符串长度
 90 
 91 --select cast(sysdate as varchar2(10)) from dual;
 92 
 93 select to_char(sysdate, ‘YYYY-MM-DD‘)from dual;  --日期转换字符串(忽略大小写)
 94 
 95 select to_char(sysdate, ‘yy-mm-dd‘)from dual; 
 96 
 97 select  to_char(sysdate, ‘YYYY-DD-MM HH24:mi:ss‘)from dual; 
 98 
 99 --select to_char(123.456,‘9999.9‘)from dual;  
100 
101 select to_date(‘2015-12-11‘,‘yyyy-mm-dd‘) from dual; --字符串转换日期
102 
103 select to_number(‘123.456‘,‘9999.999‘) from dual;
104 
105 select( nianl + gongz) from t_hq_ryxx;