Oracle学习笔记--3、基本函数的使用

    1、LOWER(列名|表达式)：把字符串转成小写

SQL> SELECT LOWER('SQL:Structural Query Language')
  2  FROM dual;

LOWER('SQL:STRUCTURALQUERYLAN
-----------------------------
sql:structural query language

备注：dual是虚表，为了满足Oracle的基本语法，查询语句必须有SELECT和FROM字句。

    2、UPPER(列名|表达式):把字符转成大写

SQL> SELECT UPPER('sql is used exclusively in rdbmses')
  2  FROM dual;

UPPER('SQLISUSEDEXCLUSIVELYINRDBMS
----------------------------------
SQL IS USED EXCLUSIVELY IN RDBMSES

    3、INITCAP(列名|表达式):把每个英文字符的首字母大写

SQL> SELECT INITCAP('SQL is an ENGLISH LIKE language')
  2  FROM dual;

INITCAP('SQLISANENGLISHLIKELANG
-------------------------------
Sql Is An English Like Language

备注：dual是虚表，为了满足Oracle的基本语法，查询语句必须有SELECT和FROM字句。

    4、CONCAT(列名|表达式,列名|表达式):把头一个字符串和后一个字符串连接成一个字符串

SQL> SELECT CONCAT('SQL allows you to manipulate the data in DB',
  2     ' without any programming knowledge')
  3  FROM dual;

CONCAT('SQLALLOWSYOUTOMANIPULATETHEDATAINDB','WITHOUTANYPROGRAMMINGKNOWLEDGE'
-----------------------------------------------------------------------------
SQL allows you to manipulate the data in DB without any programming knowledge

    5、SUBSTR(列名|表达式,m,[n])：返回指定的子串。该字串从第m个字符开始，其长度为n

SQL> SELECT SUBSTR('SQL lets you concentrate on what has to be done',14)
  2  FROM dual;

SUBSTR('SQLLETSYOUCONCENTRATEONWHA
----------------------------------
concentrate on what has to be done

SQL> SELECT SUBSTR('SQL lets you concentrate on what has to be done',14,5)
  2  FROM dual;

SUBST
-----
conce

    6、LENGTH(列名|表达式)：该函数返回列中或者表达式中列的长度。

SQL> SELECT LENGTH('SQL done not let oyou concentrate on how it will be achived')
  2  FROM dual;

LENGTH('SQLDONENOTLETOYOUCONCENTRATEONHOWITWILLBEACHIVED')
----------------------------------------------------------
                                                        59

    7、REPLACE(正文表达式，要搜寻的字符串，替换字符串)：在“正文表达式”中寻找“要搜寻的字符串”，如果找到就用”替换字符串“替换。

SQL> SELECT REPLACE('SQL*PLUS supports loops or if statements','supports',
  2     'does not support')
  3  FROM dual;

REPLACE('SQL*PLUSSUPPORTSLOOPSORIFSTATEMENTS','S
------------------------------------------------
SQL*PLUS does not support loops or if statements

    8、ROUND(列名|表达式，n)：将列名和表达式所表示的数值四舍五入到小数点后的n位。

      TRUNC(列名|表达式，n)：将列名和表达式所表示的数值截取到小数点后n位

      MOD(m,n)：将m处以n并取余数

SQL> SELECT ROUND(168.888,1),TRUNC(168.888,1)
  2  FROM dual;

ROUND(168.888,1) TRUNC(168.888,1)
---------------- ----------------
           168.9            168.8

SQL> SELECT ROUND(168.333,2),TRUNC(168.333,2)
  2  from dual;

ROUND(168.333,2) TRUNC(168.333,2)
---------------- ----------------
          168.33           168.33

SQL> SELECT ROUND(168.888,0),TRUNC(168.888,0)
  2  FROM dual;

ROUND(168.888,0) TRUNC(168.888,0)
---------------- ----------------
             169              168

SQL> SELECT MOD(1900,400)
  2  FROM dual;

MOD(1900,400)
-------------
          300

    9、ADD_MONTHS(日期，n)：把n个月加到日期上

SQL> SELECT ADD_MONTHS('10-MAR-16',6)
  2  FROM dual;

ADD_MONTHS('
------------
10-SEP-16

    10、NEXT_DAY(日期，字符串)：返回由下一个字符串（星期几）指定的日期

SQL> SELECT NEXT_DAY('10-MAR-16','MONDAY')
  2  FROM dual;

NEXT_DAY('10
------------
14-MAR-16

    11、LAST_DAY(日期)：返回该日期所在月的最后一天

SQL> SELECT LAST_DAY('10-MAR-16')
  2  FROM dual;

LAST_DAY('10
------------
31-MAR-16