# oracle常用函数

###### 字符首字母大写[initcap]、转换为小写[lower]、转换为大写[upper]：
select initcap('abc'), lower('ABC'), upper('abc') from dual; => Abc,abc,ABC
###### 返回字符串长度[length]：
select length('abc') from dual; => 3

SELECT RPAD(LPAD('abc',5,'*'),8,'*') FROM DUAL; => **abc***

SELECT TRIM(' ABC ') FROM DUAL; => 'ABC' ;
SELECT TRIM('s' FROM 'strings') FROM DUAL; => tring
SELECT LTRIM(' ABC ') FROM DUAL; => 'ABC ' ;
SELECT RTRIM(' ABC ') FROM DUAL; => ' ABC' ; 

SELECT SUBSTR('ABCDEF',2,3) FROM DUAL; => BCD  --从第2个位置开始取3个

SELECT REPLACE('aabbcc','bb','dd') FROM DUAL;  => aaddcc

SELECT ABS(100),ABS(-100) FROM DUAL; => 100 100

 SELECT AVG(DISTINCT age) FROM tableName;  --对不同的值求平均值
SELECT AVG(ALL age) FROM tableName;  --对所有值求平均值

SELECT MAX(DISTINCT age) FROM tableName;
SELECT MAX(ALL age) FROM tableName; 
SELECT MIN(DISTINCT age) FROM tableName;
SELECT MIN(ALL age) FROM tableName;

SELECT ROUND(5.5),ROUND(-5.5) FROM DUAL; => 6,-6
SELECT TRUNC(5.5),TRUNC(-5.5) FROM DUAL; => 5,-5
select trunc(123.4567,-2) trunc1,trunc(123.4567,2) from dual; => 100,123.45

SELECT SIGN(10),SIGN(-10),SIGN(0) FROM DUAL;  => 1,-1,0

SELECT CEIL(12.34) FROM DUAL;  => 13
SELECT FLOOR(12.34) FROM DUAL;  => 12


SELECT MOD(5,2),MOD(5,5),MOD(2,5) FROM DUAL;  => 1,0,2

SELECT SQRT(9) FROM DUAL; => 3

select to_char(add_months(to_date('201801','yyyymm'),2),'yyyymm') from dual; => 201803
select to_char(add_months(to_date('201801','yyyymm'),-2),'yyyymm') from dual; => 201711

select last_day(sysdate) from dual;  => 当前月最后一天

select next_day(sysdate,'星期五') from dual; => 下一个星期五

select to_char(sysdate,'yyyymmdd'),to_char((sysdate)+1,'yyyymmdd') from dual;  => 20180102,20180103

select to_date('20171226','yyyy-mm-dd') from dual; => 2017-12-26

select to_number('2018') year from dual; => 2018

SELECT age,height FROM tableName ORDER BY age,height DESC; => age升序height降序

SELECT deptno,avg(sal) FROM tableNmae GROUP BY deptno HAVING avg(sal)>1000;  => 查询平均工资大于1000的部门编号和平均工资

----------------------------------------

select ascii('A'), ascii('a'), chr(66) from dual; =>65,97,B
###### 返回指定字符的位置[instr（被搜索字符串、希望搜索的字符串、搜索开始位置、字符出现位置）]：
select instr('abcdefgd','d',1,2) from dual; =>8 (第二个 d 的位置)

----------------------------------------

decode:

select decode(5,1,'!',2,'#',3,'*','other') from dual; => other

case when:

select CASE WHEN sex = '1' THEN '男' WHEN sex = '2' THEN '女' ELSE '其他' END from tableName;

nvl:

select nvl(a,b) from dual; => a不为null返回a,a为null返回b

select concat('a','b') from dual; => ab  只能连两个
select 'a' || 'b' || 'c' from dual; => abc  可以连多个