oracle关键字整理

小写转大写UPPer

select upper(ename) from emp;

大写转小写LOWER

select lower(ename) from emp;

Clob字段转换成字符串

select dbms_lob.substr(content) from emp;

oracle字段值根据特定分隔符拆分

select *
  from (SELECT REGEXP_SUBSTR(KEYWORD, '[^ ]+', 1, level) key_word, a.*
          from test0408 a
          where id='44'
        connect by level <= regexp_count(a.KEYWORD, ' ') + 1
               and KEYWORD = prior KEYWORD
               and prior dbms_random.value is not null) t1
 order by t1.id;

substr 函数：截取字符串

语法：SUBSTR(string，start， [length])

string：表示源字符串，即要截取的字符串。

start：开始位置，从1开始查找。如果start是负数，则从string字符串末尾开始算起。

length：可选项，表示截取字符串长度。

SELECT SUBSTR('Hello SQL!', 1) FROM dual     --截取所有字符串，返回'Hello SQL!'
SELECT SUBSTR('Hello SQL!', 2) FROM dual     --从第2个字符开始，截取到末尾。返回'ello SQL!'
SELECT SUBSTR('Hello SQL!', -4) FROM dual    --从倒数第4个字符开始，截取到末尾。返回'SQL!'
SELECT SUBSTR('Hello SQL!', 3, 6) FROM dual  --从第3个字符开始，截取6个字符。返回'llo SQ'
SELECT SUBSTR('Hello SQL!', -4, 3) FROM dual --从倒数第4个字符开始，截取3个字符。返回'SQL'

instr 函数：返回子字符串在源字符串中的位置

语法：INSTR(string，child_string，[start]，[show_time])

string：表示源字符串。

child_string：子字符串，即要查找的字符串。

start：可选项，开始位置，默认从1开始。如果为负数，则从右向左检索。

show_time：可选项，表示子字符串第几次出现在源字符串当中，默认第1次，负数则报错。

--表示从源字符串'city_company_staff'中第1个字符开始查找子字符串'_'第1次出现的位置
SELECT INSTR('city_company_staff', '_') FROM dual    --返回5
 
--表示从源字符串'city_company_staff'中第5个字符开始查找子字符串'_'第1次出现的位置
SELECT INSTR('city_company_staff', '_', 5) FROM dual    --返回5
 
--表示从源字符串'city_company_staff'中第5个字符开始查找子字符串'_'第1次出现的位置
SELECT INSTR('city_company_staff', '_', 5, 1) FROM dual    --返回5
 
--表示从源字符串'city_company_staff'中第3个字符开始查找子字符串'_'第2次出现的位置
SELECT INSTR('city_company_staff', '_', 3, 2) FROM dual    --返回13
 
--start参数为-1，从右向左检索，查找'_'字符串在源字符串中第1次出现的位置
SELECT INSTR('city_company_staff', '_', -1, 1) FROM dual    --返回13
 
--start参数为-6，从右向左检索，查找'_'字符串在源字符串中第2次出现的位置
SELECT INSTR('city_company_staff', '_', -6, 2) FROM dual    --返回5

sysdate

-- 获取当前时间
select sysdate from dual;

-- 加法
select sysdate,add_months(sysdate,12) from dual; --加1年
select sysdate,add_months(sysdate,1) from dual; -- 加1月
select sysdate,to_char(sysdate+7,’yyyy-mm-dd HH24:MI:SS’) from dual; -- 加1星期
select sysdate,to_char(sysdate+1,’yyyy-mm-dd HH24:MI:SS’) from dual; -- 加1天
select sysdate,to_char(sysdate+1/24,’yyyy-mm-dd HH24:MI:SS’) from dual; -- 加1小时
select sysdate,to_char(sysdate+1/24/60,’yyyy-mm-dd HH24:MI:SS’) from dual; -- 加1分钟
select sysdate,to_char(sysdate+1/24/60/60,’yyyy-mm-dd HH24:MI:SS’) from dual; -- 加1秒

--减法
select sysdate,add_months(sysdate,-12) from dual; --减1年
select sysdate,add_months(sysdate,-1) from dual; --减1月
select sysdate,to_char(sysdate-7,’yyyy-mm-dd HH24:MI:SS’) from dual; --减1星期
select sysdate,to_char(sysdate-1,’yyyy-mm-dd HH24:MI:SS’) from dual; --减1天
select sysdate,to_char(sysdate-1/24,’yyyy-mm-dd HH24:MI:SS’) from dual; --减1小时
select sysdate,to_char(sysdate-1/24/60,’yyyy-mm-dd HH24:MI:SS’) from dual; --减1分钟
select sysdate,to_char(sysdate-1/24/60/60,’yyyy-mm-dd HH24:MI:SS’) from dual; --减1秒

-- 取得当前日期是本月的第几周
select to_char(sysdate,’YYYYMMDD W HH24:MI:SS’) from dual;
select to_char(sysdate,’W’) from dual;

-- 取得当前日期是一个星期中的第几天，星期日为第一天
select sysdate,to_char(sysdate,’D’) from dual;
select to_char(sysdate,’yyyy’) from dual; --年
select to_char(sysdate,’Q’ from dual; -- 季
select to_char(sysdate,’mm’) from dual; -- 月
select to_char(sysdate,’dd’) from dual; -- 日
--- ddd 年中的第几天
-- WW 年中的第几个星期
-- W 该月中第几个星期
-- D 周中的星期几
-- hh 小时(12)
-- hh24 小时(24)
-- Mi 分
-- ss 秒

--取当前日期是星期几，中文显示
select to_char(sysdate,’day’) from dual;

-- 如果一个表在一个date类型的字段上面建立了索引，如何使用
alter session set NLS_DATE_FORMAT=’YYYY-MM-DD HH24:MI:SS’

-- 得到当天凌晨0点0分0秒的日期
select trunc(sysdate) from dual;

-- 得到这天的最后一秒
select trunc(sysdate) + 0.99999 from dual;
-- 得到小时的具体数值
select trunc(sysdate) + 1/24 from dual;
select trunc(sysdate) + 7/24 from dual;

-- 得到明天凌晨0点0分0秒的日期
select trunc(sysdate+1) from dual;
select trunc(sysdate)+1 from dual;

-- 本月一日的日期
select trunc(sysdate,’mm’) from dual;

-- 得到下月一日的日期
select trunc(add_months(sysdate,1),’mm’) from dual;

-- 返回当前月的最后一天
select last_day(sysdate) from dual;
select last_day(trunc(sysdate)) from dual;
select trunc(last_day(sysdate)) from dual;
select trunc(add_months(sysdate,1),’mm’) - 1 from dual;

-- 得到一年的每一天
select trunc(sysdate,’yyyy’)+ rn -1 date0
from
(select rownum rn from all_objects
where rownum<366);

-- 今天是今年的第N天
SELECT TO_CHAR(SYSDATE,’DDD’) FROM DUAL;

-- 如何在给现有的日期加上2年
select add_months(sysdate,24) from dual;

-- 判断某一日子所在年分是否为润年
select decode(to_char(last_day(trunc(sysdate,’y’)+31),’dd’),’29’,’闰年’,’平年’) from dual;

-- 判断两年后是否为润年
select decode(to_char(last_day(trunc(add_months(sysdate,24),’y’)+31),’dd’),’29’,’闰年’,’平年’) from dual;

-- 得到日期的季度
select ceil(to_number(to_char(sysdate,’mm’))/3) from dual;
select to_char(sysdate, ‘Q’) from dual;