Oracle sql 单行函数

作者: 我爱矿泉水 | 来源:发表于2018-02-21 21:48 被阅读7次

一.字符

1.大小写转换

LOWER 字符转为小写
UPPER 字符转为大写
INITCAP 首字母大写，其他小写

SQL> select ename from emp where deptno=10;
ENAME
----------
CLARK
KING
MILLER

1).将ename转换为小写

SQL> select lower(ename) from emp where deptno=10; 
LOWER(ENAM
----------
clark
king
miller

2).将king scott转换为大写

SQL> select upper('king scott') from dual;
UPPER('KIN
----------
KING SCOTT

3).将ename首字母转为大写，其余转为小写

SQL> select initcap(ename) from emp where deptno=10;
INITCAP(EN
----------
Clark
King
Miller

SQL> select initcap('king scott') from dual; => King Scott
SQL> select initcap('king0scott') from dual; => King0scott
SQL> select initcap('king_scott') from dual; => King_Scott

2.字符处理类

1).concat：类似“||”,连接函数

SQL> select ename||' is work  '||job from emp where deptno=10;

ENAME||'ISWORK'||JOB
-----------------------------
CLARK is work  MANAGER
KING is work  PRESIDENT
MILLER is work  CLERK

SQL> select concat(ename,' is work '), job  from emp where deptno=10;

CONCAT(ENAME,'ISWOR JOB
------------------- ---------
CLARK is work       MANAGER
KING is work        PRESIDENT
MILLER is work      CLERK

SQL> select concat(concat(ename,' is work '),job)  from emp where deptno=10;

CONCAT(CONCAT(ENAME,'ISWORK'
----------------------------
CLARK is work MANAGER
KING is work PRESIDENT
MILLER is work CLERK

2).substr(expr字符串,m截取开始位置,n截取长度) //返回截取的字

注意：m为负数时，表示从右端开始截取; 必要条件：n>0。

# 截取左起第二个字符为A的ename

SQL> select ename from emp where substr(ename,2,1)='A';
ENAME
----------
WARD
MARTIN
JAMES

# substr(ename,1,1)和substr(ename,0,1)，0和1都表示从第一个字符开始截取

SQL> select ename from emp where substr(ename,1,1)='A';
SQL> select ename from emp where substr(ename,0,1)='A';
ENAME
----------
ALLEN
ADAMS

# (ename,-1,1) -1表示从右边第一个字符开始截取

SQL> select ename from emp where substr(ename,-1,1)='K';
ENAME
----------
CLARK

SQL> select ename from emp where substr(ename,-2,1)='K';
ENAME
----------
BLAKE

注意：

SQL> select ename from emp where substr(ename,1)='KING'; #无意义
SQL> select ename from emp where ename='KING'; #正确的查询方法
ENAME
----------
KING

3).length(str) 长度

# LENGTH(str) 返回以字符为单位的长度.
# LENGTHB(str) 返回以字节为单位的长度,unicode格式一个汉字占三个字节数
# LENGTHC(str) 返回以Unicode完全字符为单位的长度.
# LENGTH2(str) 返回以UCS2代码点为单位的长度.
# LENGTH4(str) 返回以UCS4代码点为单位的长度.

SQL> select ename,length(ename),lengthc(ename),
lengthb(ename) from emp where deptno=10;
ENAME      LENGTH(ENAME) LENGTHC(ENAME) LENGTHB(ENAME)
---------- ------------- -------------- --------------
CLARK              5          5          5
KING               4          4          4
MILLER             6          6          6

SQL> insert into emp(empno,ename) values(1,'长度');
SQL> select ename,length(ename),lengthc(ename),
lengthb(ename) from emp where empno=1;
ENAME      LENGTH(ENAME) LENGTHC(ENAME) LENGTHB(ENAME)
---------- ------------- -------------- --------------
长度           2      2       6 #用unicode格式存储汉字，三个字节一个汉字

4).instr(str,'A') 查找A在str里第一次出现的位置

SQL> select instr(ename,'A'),ename  from emp;
# 查找A出现的位置
INSTR(ENAME,'A') ENAME
---------------- ----------
           0 SMITH
           1 ALLEN
           2 WARD
           0 JONES
           2 MARTIN
           3 BLAKE
           3 CLARK
           0 SCOTT
           0 KING
           0 TURNER
# 查找A开头的ename
SQL> select ename from  emp where instr(ename,'A')=1;
ENAME
----------
ALLEN
ADAMS

SQL> insert into emp(empno,ename) values(2,'AADCS');
已创建 1 行。
SQL> select ename from  emp where instr(ename,'A')=2;
ENAME
----------
WARD
MARTIN
JAMES #这里并没有查询到AADCS这个值，因为instr只查询A第一次出现的位置

SQL> select ename from  emp where instr(substr(ename,2),'A')=1;
ENAME
----------
AADCS  # 这时，便查到了AADCS
WARD
MARTIN
JAMES

SQL> select instr(ename,'LL'),ename  from emp;
INSTR(ENAME,'LL') ENAME      # LL 这里当作一个整体，ALLEN返回值2,MILLER返回值3.
----------------- ----------
        0 AADCS
        0 SMITH
        2 ALLEN
        3 MILLER

5).LPAD and RPAD 左填充和右填充

SQL> select * from dept;

    DEPTNO DNAME      LOC
---------- -------------- -------------
    10 ACCOUNTING     NEW YORK
    20 RESEARCH   DALLAS
    30 SALES      CHICAGO
    40 OPERATIONS     BOSTON

SQL> select rpad(deptno,10,' ') deptno,dname,loc from dept;
# 右边填充左边对齐，最大10个字符长度，不足位数用空格填充。
DEPTNO                   DNAME      LOC
---------------------------------------- -------------- -------------
10                   ACCOUNTING NEW YORK
20                   RESEARCH   DALLAS
30                   SALES      CHICAGO
40                   OPERATIONS BOSTON

# 左边填充右边对齐
SQL> col dname for a15
SQL> select deptno,lpad(dname,15,' ') dname,loc from dept;

    DEPTNO DNAME       LOC
---------- --------------- -------------
    10  ACCOUNTING NEW YORK
    20    RESEARCH DALLAS
    30       SALES CHICAGO
    40  OPERATIONS BOSTON

# 列的值居左left/右right/中center
SQL> col dname just right 
SQL> select deptno,lpad(dname,15,' ') dname,loc from dept;

    DEPTNO       DNAME LOC
---------- --------------- -------------
    10  ACCOUNTING NEW YORK
    20    RESEARCH DALLAS
    30       SALES CHICAGO
    40  OPERATIONS BOSTON

6).TRIM 删除字符函数

trim(both|leading|trailing 'char' from expr)
trim('A' from 'AAABBCDDCAAADDDAA') => BBCDDCAAADDD #从两头删除字符A
trim('A' from 'AAABBCDDCAAADDDAA') => BBCDDCAAADDD #同上，所以both可以省略
trim(leading 'A' from 'AAABBCDDCAAADDDAA') => BBCDDCAAADDDAA #删除左边的字符A
trim(trailing 'A' from 'AAABBCDDCAAADDDAA') => AAABBCDDCAAADDD #删除右边的字符A

ltrim、rtrim，要求：oracle版本>=10g
ltrim('AAABBCDDCAAADDDAA','A') => BBCDDCAAADDDAA 类似于trim leading 删除左边的A
rtrim('AAABBCDDCAAADDDAA','A') => AAABBCDDCAAADDD 类似于trim trailing 删除右边的A

7).replace(str,old,new) 替换函数

replace('BLACK and BLUE','BL','J') => JACK and JUE

SQL> select replace(ename,'长度','forway') neweanme,ename from emp;
NEWEANME                             ENAME
------------------------------------------------------------ ----------
forway                                                       长度
SMITH                                SMITH
ALLEN                                ALLEN
WARD                                 WARD
JONES                                JONES
MARTIN                               MARTIN
BLAKE                                BLAKE

二.number数字函数

1.round和trunc

example：456.789
4   5  6 . 7 8 9 # 注意位置对应关系
-3 -2 -1 0 1 2 3

round(456.789,2) => 8>5 456.79 #保留2位小数
trunc(456.789,2) => 只舍掉不进位 456.78 #保留2位小数

round(456.789,1) => 456.8 #保留1位小数
trunc(456.789,1) => 456.7 #保留1位小数

round(456.789,-1) => 460
trunc(456.789,-1) => 450

round(456.789,-2) => 500
trunc(456.789,-2) => 400

#不包含取舍位数要求的时候：
ROUND(456.789) => 457
TRUNC(456.789) => 456

2.mod(number,a) 取余

MOD(2002,5) => 2
MOD(2002,3) => 3

3.abs 绝对值

abs(-123) => 123

三.日期

1.日期显示格式

oracle里日期默认存储格式为DD-MON-RR

[oracle@wyzc ~]$ unset NLS_LANG
[oracle@wyzc ~]$ sqlplus scott/scott@wyzc11g
SQL> select ename,hiredate from emp;
ENAME      HIREDATE
---------- ---------
JONES      02-APR-81
MARTIN     28-SEP-81
BLAKE      01-MAY-81
CLARK      09-JUN-81
SCOTT      19-APR-87
KING       17-NOV-81
SQL> select to_char(hiredate,'yyyy-mm-dd') from emp;
TO_CHAR(HI
----------
1980-12-17
1981-02-20
1981-02-22
1981-04-02
1981-09-28
1981-05-01
SQL> select to_char(sysdate,'yyyy-mm-dd') from dual; => 2018-02-21

RR纪年法年份计算：
current year last 2 number --- <50
last 2 number --- 0-49 <50
last 2 number --- 50-99 世纪 -1

    last 2 number  --- >=50
    last 2 number  --- >=50 
    last 2 number  --- < 50  0-49 世纪 +1

# 更改默认的日期显示格式

SQL> alter session set nls_date_format='yyyy-mm-dd'; 

SQL> select hiredate from emp where deptno=10;
HIREDATE
----------
1981-06-09
1981-11-17
1982-01-23

2.日期函数

1).日期的四舍五入 round/trunc

SQL> select ename,sysdate-hiredate  hiredays from emp;# 计算雇佣天数
ENAME        HIREDAYS
---------- ----------
SMITH      13580.5862
ALLEN      13515.5862
WARD       13513.5862
JONES      13474.5862

# today 2018-02-21
SQL> select sysdate-1 from dual; => 2018-02-20
SQL> select sysdate+1 from dual; => 2018-02-22
SQL> select round(sysdate) from dual; => 2018-02-22 四舍五入
select round(sysdate,'yyyy') from dual; => 2018-01-01

临时修改日期、时间
sudo date -s MM/DD/YY //修改日期
sudo date -s hh:mm:ss //修改时间

在修改时间以后，修改硬件CMOS的时间
sudo hwclock --systohc //非常重要，如果没有这一步的话，后面时间还是不准

# today 2013-12-31 12:02:03 星期二

SQL> select round(sysdate,'yyyy') ,trunc(sysdate,'yyyy') from dual;
ROUND(SYSD TRUNC(SYSD
---------- ----------
2014-01-01 2013-01-01

SQL> select round(sysdate,'mm') ,trunc(sysdate,'mm') from dual;
ROUND(SYSD TRUNC(SYSD
---------- ----------
2014-01-01 2013-12-01 # 27号超过了15号，round=>2014-01-01,trunc=>2013-12-01

SQL> select round(sysdate,'dd') ,trunc(sysdate,'dd') from dual;
ROUND(SYSD TRUNC(SYSD
---------- ----------
2014-01-01 2013-12-31 # 12:02:03 超过半天，所以round=>2014-01-01

# 29号是这个星期的第三天，不超一周的一半，结果也就是上个星期的周日，国外从周日开始算起

SQL> select round(sysdate,'day') ,trunc(sysdate,'day') from dual;
ROUND(SYSD TRUNC(SYSD
---------- ----------
2013-12-29 2013-12-29

2).months_between、add_months、next_day、last_day

months_between、add_months

# 员工入职公司多少月
SQL> select ename,months_between(sysdate,hiredate) months from emp; 
# 员工入职公司多少年
SQL> select ename,months_between(sysdate,hiredate)/12 years from emp; 
#员工入职、转正的日期
SQL> select ename,hiredate,add_months(hiredate,3) from emp; 
ENAME      HIREDATE   ADD_MONTHS
---------- ---------- ----------
SMITH      1980-12-17 1981-03-17
ALLEN      1981-02-20 1981-05-20
WARD       1981-02-22 1981-05-22
JONES      1981-04-02 1981-07-02
MARTIN     1981-09-28 1981-12-28

SQL> update emp set hiredate=to_date('1980-11-30','yyyy-mm-dd'),ename='E3' 
where empno=1;
# add_months 只对月份累加运算，下文这里2月份只有28天，只到28号，所以这里就是1981-02-28
SQL> select ename,hiredate,add_months(hiredate,3) from emp;
ENAME      HIREDATE   ADD_MONTHS
---------- ---------- ----------
E3     1980-11-30 1981-02-28
SMITH      1980-12-17 1981-03-17

# 本月转正的员工
SQL> select ename,hiredate from emp where hiredate=add_months(sysdate,-3);

next_day、last_day

# 下个星期一，也可以写全monday
SQL> select next_day(sysdate,'mon') from dual; => 26-FEB-18 

#换到中文环境下，./.nls,查询下个星期三
SQL> select next_day(sysdate,'星期三') from dual; => 28-2月 -18
# 查询当天是星期几
SQL> select to_char(sysdate,'day') from dual; => 星期三
SQL> select to_char(sysdate,'dy') from dual; # 同上
SQL> select last_day(sysdate) from dual; => 28-2月-18 #当月最后一天