--oracle常用函数积累--

--1、字符串长度：LENGTH ，语法： CONCAT（string）

    --示例

    select LENGTH('AA_BB') from dual;--结果：5

--2、字符串连接：CONCAT，语法： CONCAT（string1,string2）

    --示例

    select CONCAT('AA','_BB') from dual;--结果：AA_BB

    --简单点，用双竖线即可

    select 'AA'||'''-'''||'BB' from dual;--结果：AA'-'BB，单引号要用''转义

--3、字符串替换：REPLACE，语法：REPLACE（string，search_str[,replace_str]）；

    --示例

    select REPLACE('AA_BB','BB','CC') from dual;--结果：AA_CC

    select REPLACE('AA_BBBB','BB') from dual;--结果：AA_

--4、字符串截取：SUBSTR，语法: SUBSTR(string,start_position,[length])，string 源字符串，start_position开始位置（从0开始）， length 可选项，截取的个数

    --示例

    select SUBSTR('AA_BB',0,2) from dual;--结果：AA

    select SUBSTR('AA_BB',0) from dual;--结果：AA_BB

--5、字符串查询：INSTR（string,subString,start_position,count），string：源字符串，subString：要查找的子字符串，start_position：查找的开始位置（从1开始），count：源字符串中第几次出现的子字符串

    --示例

    select INSTR('AA_BB_BB','A',1,1) from dual;--结果：1

    select INSTR('AA_BB_BB','A',1,2) from dual;--结果：2

    select INSTR('AA_BB_BB','BB',1,2) from dual;--结果：7

--6、一列结果拼接成一个字符串listagg，语法: listagg(column,str) within group(order by column),column要查找的表字段，str连接字符串

    --示例

    --3.1、查询table4表的c_id数据

    select c_id from table4;

    --结果为：

    --C_ID

    --0BC86FE5-BCF3-45C2-95C1-C8328D5849FD

    --39430D20-13BB-4EEB-A300-82F6AC7551CB

    --97876BB9-F366-4694-BFD6-71C3ACBD5995

    --9C085EE3-399E-4773-8A91-8D745E1713A6

    --3.2、使用函数listagg（oracle版本11g或更高）

     select listagg(c_id,',') within group(order by c_id) as res

          from table4;

     --结果为：

     --RES

     --0BC86FE5-BCF3-45C2-95C1-C8328D5849FD,39430D20-13BB-4EEB-A300-82F6AC7551CB,97876BB9-F366-4694-BFD6-71C3ACBD5995,9C085EE3-399E-4773-8A91-8D745E1713A6

--7、使用to_char、to_date、to_timestamp，时间加减等

    --示例

    select to_char(sysdate,'yyyy') from dual;--结果：2017

    select to_char(sysdate,'mm') from dual;--结果：08

    select to_char(sysdate,'dd') from dual;--结果：25

    select to_char(sysdate,'yyyy-mm-dd') from dual;--结果：2017-08-25

    select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;--结果：2017-08-25 17:01:22

    select to_date('2017-08-25 17:01:22','yyyy-mm-dd hh24:mi:ss') from dual; --结果：25-8月 -17

    select to_timestamp('2017-08-22 09:26:15:821000', 'YYYY-MM-DD HH24:MI:SS:FF6') from dual; --结果：22-8月 -17 09.26.15.821000000 上午

    select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate - interval '' MINUTE,'yyyy-mm-dd hh24:mi:ss') from dual;--结果：2017-08-25 17:05:41     2017-08-25 16:55:41 减去10分钟

    select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate + interval '' MINUTE,'yyyy-mm-dd hh24:mi:ss') from dual;--结果：2017-08-25 17:06:44    2017-08-25 17:16:44 加上10分钟

    select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate - interval '' HOUR,'yyyy-mm-dd hh24:mi:ss') from dual;--减去10小时

    select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate + interval '' HOUR,'yyyy-mm-dd hh24:mi:ss') from dual;--加上10小时

    select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate - interval '' MONTH,'yyyy-mm-dd hh24:mi:ss') from dual;--减去10月

    select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate + interval '' MONTH,'yyyy-mm-dd hh24:mi:ss') from dual;--加上10月

    select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate - interval '' YEAR,'yyyy-mm-dd hh24:mi:ss') from dual;--减去10年

    select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate + interval '' YEAR,'yyyy-mm-dd hh24:mi:ss') from dual;--加上10年

    select to_char(sysdate-1,'yyyy-mm-dd') from dual;--结果：2017-08-24，减去一天

    select to_char(sysdate+1,'yyyy-mm-dd') from dual;--结果：2017-08-26，加去一天

--8、空判断：nvl，语法:nvl(column,str)，column被判断字符，如果为null，则替换为str

    --示例

    select nvl(null,'无') from dual;--结果：无

--9、条件判断：case when then else end ，CASE search_expression WHEN expression1 THEN result1 WHEN expressionN THEN resultN　ELSE default_result

    --示例

    select case when 'a'='a' then '优' when 'a'='b' then '良' else '合格' end

    from dual;--结果：优
--10截取clob部分数据

　　dbms_lob.substr(column,4000)