单行函数

UPPER输出大写字母
LOWER输出小写字母
用 || 连接字符串和列
输出一个短语。

SQL> select 'the job for '||UPPER(ename)||' is  '||LOWER(job) as "employee details"
  2  from emp;

employee details
------------------------------------
the job for SMITH is  clerk
the job for ALLEN is  salesman
the job for WARD is  salesman
the job for JONES is  manager
the job for MARTIN is  salesman
the job for BLAKE is  manager
the job for CLARK is  manager
the job for SCOTT is  analyst
the job for KING is  president
the job for TURNER is  salesman
the job for ADAMS is  clerk
the job for JAMES is  clerk
the job for FORD is  analyst
the job for MILLER is  clerk

14 rows selected.

concat连接两个字符或者字符串

SQL> select concat('Hello','World') from dual;

CONCAT('HE
----------
HelloWorld

substr就是把字符串的从第一位开始数五位输出，就是说出h-e-l-l-o。

SQL> select substr('HelloWorld',1,5) from dual;

SUBST
-----
Hello

设置时间格式输出为年-月-日

SQL> alter session set nls_date_format='YYYY-MM-DD'；
Session altered.

从倒数第一位开始数三位，那应该还是最后一位。

SQL> select job , substr(job,-1,3)
  2  from emp;

JOB       SU
--------- --
CLERK     K
SALESMAN  N
SALESMAN  N
MANAGER   R
SALESMAN  N
MANAGER   R
MANAGER   R
ANALYST   T
PRESIDENT T
SALESMAN  N
CLERK     K
CLERK     K
ANALYST   T
CLERK     K

14 rows selected.

如果后两位都是负数的话，那么就没有输出。
length函数是计算字符串的长度。

SQL> select length('helloworld') from emp;

LENGTH('HELLOWORLD')
--------------------
                  10
                  10
                  10
                  10
                  10
                  10
                  10
                  10
                  10
                  10
                  10
                  10
                  10
                  10

14 rows selected.

按从小到大顺序排列姓名长度。

SQL> select ename , length(ename)      
  2  from emp
  3  order by length(ename);

SQL> select ename , length(ename) name
  2  from emp
  3  order by name;

SQL> select ename , length(ename)
  2  from emp
  3  order by 2;

ENAME      LENGTH(ENAME)
---------- -------------
FORD                   4
KING                   4
WARD                   4
BLAKE                  5
SCOTT                  5
JONES                  5
ALLEN                  5
SMITH                  5
CLARK                  5
JAMES                  5
ADAMS                  5
MARTIN                 6
TURNER                 6
MILLER                 6

14 rows selected.

从字符串中查找A的位置。

SQL> select instr(ename , 'A')
  2  from emp;

ENAME      INSTR(ENAME,'A')
---------- ----------------
SMITH                     0
ALLEN                     1
WARD                      2
JONES                     0
MARTIN                    2
BLAKE                     3
CLARK                     3
SCOTT                     0
KING                      0
TURNER                    0
ADAMS                     1
JAMES                     2
FORD                      0
MILLER                    0



14 rows selected.

保留小数

保留两位小数
SQL> select round(45.926,2) from dual;

ROUND(45.926,2)
---------------
          45.93

保留到个位或者0位
SQL> select round(45.926,0) from dual;

ROUND(45.926,0)
---------------
             46

保留到-1位，就是保留到十位。
SQL> 
SQL> select round(45.926,-1) from dual;

ROUND(45.926,-1)
----------------
              50

trunc函数是显示第几位就直接输出第几位的数，无序保留小数。
SQL> select trunc(45.926,2) from dual;

TRUNC(45.926,2)
---------------
          45.92
保留-1位。
SQL> select trunc(45.926,-1) from dual;

TRUNC(45.926,-1)
----------------
              40

系统时间减去入职日期。

SQL> select sysdate-hiredate from emp;

SYSDATE-HIREDATE
----------------
      14035.6297
      13970.6297
      13968.6297
      13929.6297
      13750.6297
      13900.6297
      13861.6297
      11721.6297
      13700.6297
      13770.6297
      11687.6297
      13684.6297
      13684.6297
      13633.6297

14 rows selected.

按星期输出系统时间与入职日期的差

QL> select (sysdate-hiredate)/7 from emp;

(SYSDATE-HIREDATE)/7
--------------------
          2005.09123
          1995.80551
           1995.5198
          1989.94837
          1964.37694
          1985.80551
          1980.23408
           1674.5198
          1957.23408
          1967.23408
          1669.66266
          1954.94837
          1954.94837
          1947.66266

14 rows selected.

系统日期输出下一个星期三

SQL> select next_day(sysdate,'wednesday') from dual;

NEXT_DAY(S
----------
2019-05-29

to——char把日期转化为字符串。

SQL> select ename
  2  , to_char(hiredate,'MON-DD')
  3  , to_char(hiredate,'MM-DD')
  4  from emp;

ENAME      TO_CHAR(H TO_CH
---------- --------- -----
SMITH      DEC-17    12-17
ALLEN      FEB-20    02-20
WARD       FEB-22    02-22
JONES      APR-02    04-02
MARTIN     SEP-28    09-28
BLAKE      MAY-01    05-01
CLARK      JUN-09    06-09
SCOTT      APR-19    04-19
KING       NOV-17    11-17
TURNER     SEP-08    09-08
ADAMS      MAY-23    05-23
JAMES      DEC-03    12-03
FORD       DEC-03    12-03
MILLER     JAN-23    01-23

14 rows selected.