Oracle函数系列学习笔记

常用函数：

sum()、nvl()、decode()、case when 、substr()、instr()、

eg: substr  case when、to_char
	and substr(t2.project_cd, 2, 2) >= case when to_char(sysdate,'mm')>'01' then to_char(sysdate,'yy') else 	     		        to_char(add_months(sysdate, -12), 'yy') end
eg2: instr、
 from t_pc_project_moneymaintain t1
                   join v_project_station_money t2
                   on t1.citycode = t2.citycode and instr(t1.sub_project_collection_id,t2.sub_project_collection_id) != 0
  eg3: inv、decode
        nvl(decode(t2.Status, 102, t2.PROJECT_INV_AT, 0), 0) +
        nvl(decode(t2.Status, 106, t2.PROJECT_INV_AT, 0), 0) +
        nvl(decode(t2.Status, 110, t2.PROJECT_INV_AT, 0), 0) +
        nvl(decode(t2.Status, 111, t2.PROJECT_INV_AT, 0), 0) as PROJECT_USEDINVAT,

—判断函数：nvl()、decode（）、 case when、

参考：https://blog.csdn.net/baoliangsheng/article/details/46862675
NVL( string1, replace_with)　　功能：如果string1为NULL，则NVL函数返回replace_with的值，否则返回string1的值。
NVL2(E1, E2, E3)的功能为：如果E1为NULL，则函数返回E3，若E1不为null，则返回E2。
decode(条件,值1,翻译值1,值2,翻译值2,…值n,翻译值n,缺省值);
case when:

SELECT  
CASE WHEN salary <= 500 THEN '1'  
WHEN salary > 500 AND salary <= 600  THEN '2'  
WHEN salary > 600 AND salary <= 800  THEN '3'  
WHEN salary > 800 AND salary <= 1000 THEN '4'  
ELSE NULL END salary_class, -- 别名命名
COUNT(*)  
FROM    Table_A  
GROUP BY  
CASE WHEN salary <= 500 THEN '1'  
WHEN salary > 500 AND salary <= 600  THEN '2'  
WHEN salary > 600 AND salary <= 800  THEN '3'  
WHEN salary > 800 AND salary <= 1000 THEN '4'  
ELSE NULL END;  

—字符串函数

–字符串查找函数 索引从1 开始，返回目标字符串在要查找的字符串中所在的位置,找不到返回0.

select instr(‘abcdefg’,‘c’)from dual;

–字符串截取函数

select substr(‘abcdefg’,1,2) from dual;–索引从1 开始 ，从第一位开始截取，截取两位
select substr(‘abcdefg’,2) from dual;–索引从1 开始，从第二位开始截取，一直截取到最后
–按字节来计算则可以采用substrb函数
–substrb(‘智能ABC’,3,4)=‘能AB’
–substrc:按Unicode编码,
–substr2:按UCS2编码,
–substr4:按UCS4编码。

–字符替换函数

select replace(‘abcdafg’,‘a’,‘H’)from dual;

–去空函数

select ltrim(’ a b ce ‘)from dual;–左去空
select rtrim(’ a b ce ‘)from dual;–右去空
select trim(’ a b ce ')from dual;–去前后空

–补位函数

select lpad(‘a’,3,‘b’)from dual;–左补位
select lpad(‘a’,2,‘b’)from dual;
select lpad(‘a’,1,‘b’)from dual;

select rpad(‘a’,3,‘b’)from dual;–右补位
select rpad(‘a’,2,‘b’)from dual;
select rpad(‘a’,1,‘b’)from dual;

–将字符串转换为大写

select upper(‘abc’) from dual;–全部大写
select lower(‘ABC’)from dual;–全部小写
select INITCAP(‘abc’)from dual;–首字母大写

–拼接字符串

select ‘abc’||‘def’ from dual;
select username||password from t_user;
select concat(username,password)from t_user;

–列的宽度、长度(列值的宽度)

select length(username)from t_user;