《卸甲筆記》-單行函式對比之一

宋躍傑發表於2016-06-21

1驗證UPPER()、LOWER()函式
Oracle

SQL> select UPPER(`SongYuejie`),LOWER(`VASTDATA`) from dual;

UPPER(`SONGYUEJIE`)  LOWER(`VASTDATA`
-------------------- ----------------
SONGYUEJIE         vastdata

PPAS

scott=# select UPPER(`SongYuejie`),LOWER(`VASTDATA`) from dual;
   upper    |  lower   
------------+----------
 SONGYUEJIE | vastdata
(1 row)

2查詢出僱員姓名是SMITH的完整資訊，但是由於失誤沒有考慮到資料的大小寫問題，此時可以使用UPPER()函式將全部內容變為大寫
Oracle

SQL> select * from emp where ename=UPPER(`smith`);

     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7369 SMITH        CLERK             7902 17-DEC-80
       800              20

PPAS

scott=# select * from emp where ename=UPPER(`smith`);
 empno | ename |  job  | mgr  |      hiredate      |  sal   | comm | deptno 
-------+-------+-------+------+--------------------+--------+------+--------
  7369 | SMITH | CLERK | 7902 | 17-DEC-80 00:00:00 | 800.00 |      |     20
(1 row)

3查詢所有僱員的姓名，要求將每個僱員的姓名以首字母大寫的形式出現
Oracle

SQL> select ename 原始姓名,INITCAP(ename) 姓名開頭首字母大寫 from emp; 

原始姓名         姓名開頭首字母大寫
-------------------- --------------------
SMITH             Smith
ALLEN             Allen
WARD             Ward
JONES             Jones
MARTIN             Martin
BLAKE             Blake
CLARK             Clark
SCOTT             Scott
KING             King
TURNER             Turner
ADAMS             Adams

原始姓名         姓名開頭首字母大寫
-------------------- --------------------
JAMES             James
FORD             Ford
MILLER             Miller

14 rows selected.

PPAS

scott=# select ename 原始姓名,INITCAP(ename) 姓名開頭首字母大寫 from emp; 
 原始姓名 | 姓名開頭首字母大寫 
----------+--------------------
 SMITH    | Smith
 ALLEN    | Allen
 WARD     | Ward
 JONES    | Jones
 MARTIN   | Martin
 BLAKE    | Blake
 CLARK    | Clark
 SCOTT    | Scott
 KING     | King
 TURNER   | Turner
 ADAMS    | Adams
 JAMES    | James
 FORD     | Ford
 MILLER   | Miller
(14 rows)

4查詢出所有僱員的姓名，並且將僱員姓名中所有的字母“A”替換成“_”
Oracle

SQL> select ename,REPLACE(ename,`A`,`_`) from emp;

ENAME             REPLACE(ENAME,`A`,`_
-------------------- --------------------
SMITH             SMITH
ALLEN             _LLEN
WARD             W_RD
JONES             JONES
MARTIN             M_RTIN
BLAKE             BL_KE
CLARK             CL_RK
SCOTT             SCOTT
KING             KING
TURNER             TURNER
ADAMS             _D_MS

ENAME             REPLACE(ENAME,`A`,`_
-------------------- --------------------
JAMES             J_MES
FORD             FORD
MILLER             MILLER

14 rows selected.

PPAS

scott=# select ename,REPLACE(ename,`A`,`_`) from emp;
 ename  | replace 
--------+---------
 SMITH  | SMITH
 ALLEN  | _LLEN
 WARD   | W_RD
 JONES  | JONES
 MARTIN | M_RTIN
 BLAKE  | BL_KE
 CLARK  | CL_RK
 SCOTT  | SCOTT
 KING   | KING
 TURNER | TURNER
 ADAMS  | _D_MS
 JAMES  | J_MES
 FORD   | FORD
 MILLER | MILLER
(14 rows)

5查詢書姓名長度是5的所有僱員的資訊
Oracle

SQL>  select * from emp where length(ename)=5;

     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7369 SMITH        CLERK             7902 17-DEC-80
       800              20

      7499 ALLEN        SALESMAN         7698 20-FEB-81
      1600      300          30

      7566 JONES        MANAGER          7839 02-APR-81
      2975              20


     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7698 BLAKE        MANAGER          7839 01-MAY-81
      2850              30

      7782 CLARK        MANAGER          7839 09-JUN-81
      2450              10

      7788 SCOTT        ANALYST          7566 19-APR-87
      3000              20


     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7876 ADAMS        CLERK             7788 23-MAY-87
      1100              20

      7900 JAMES        CLERK             7698 03-DEC-81
       950              30


8 rows selected.

PPAS

scott=# select * from emp where length(ename)=5;
 empno | ename |   job    | mgr  |      hiredate      |   sal   |  comm  | deptno 
-------+-------+----------+------+--------------------+---------+--------+--------
  7369 | SMITH | CLERK    | 7902 | 17-DEC-80 00:00:00 |  800.00 |        |     20
  7499 | ALLEN | SALESMAN | 7698 | 20-FEB-81 00:00:00 | 1600.00 | 300.00 |     30
  7566 | JONES | MANAGER  | 7839 | 02-APR-81 00:00:00 | 2975.00 |        |     20
  7698 | BLAKE | MANAGER  | 7839 | 01-MAY-81 00:00:00 | 2850.00 |        |     30
  7782 | CLARK | MANAGER  | 7839 | 09-JUN-81 00:00:00 | 2450.00 |        |     10
  7788 | SCOTT | ANALYST  | 7566 | 19-APR-87 00:00:00 | 3000.00 |        |     20
  7876 | ADAMS | CLERK    | 7788 | 23-MAY-87 00:00:00 | 1100.00 |        |     20
  7900 | JAMES | CLERK    | 7698 | 03-DEC-81 00:00:00 |  950.00 |        |     30
(8 rows)

6查詢姓名前3個字母是JAM的僱員資訊
Oracle

SQL>  select * from emp where SUBSTR(ename,0,3)=`JAM`;

     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7900 JAMES        CLERK             7698 03-DEC-81
       950              30

PPAS

scott=# select * from emp where SUBSTR(ename,0,3)=`JAM`;
 empno | ename |  job  | mgr  |      hiredate      |  sal   | comm | deptno 
-------+-------+-------+------+--------------------+--------+------+--------
  7900 | JAMES | CLERK | 7698 | 03-DEC-81 00:00:00 | 950.00 |      |     30
(1 row)

7查詢所有10部門僱員姓名，但是不顯示每個僱員姓名的前三個字母
Oracle

SQL> select ename 原姓名,SUBSTR(ename,3) 擷取之後的姓名 from emp where deptno=10;

原姓名
--------------------
擷取之後的姓名
----------------------------------------------------------------
CLARK
ARK

KING
NG

MILLER
LLER

PPAS

scott=# select ename 原姓名,SUBSTR(ename,3) 擷取之後的姓名 from emp where deptno=10;
 原姓名 | 擷取之後的姓名 
--------+----------------
 CLARK  | ARK
 KING   | NG
 MILLER | LLER
(3 rows)

8要求顯示每個僱員姓名及其姓名的後3個字母
Oracle

SQL> select ename,SUBSTR(ename,LENGTH(ename)-2) from emp;

ENAME
--------------------
SUBSTR(ENAME,LENGTH(ENAME)-2)
--------------------------------------------------------------------------------
SMITH
ITH

ALLEN
LEN

WARD
ARD


ENAME
--------------------
SUBSTR(ENAME,LENGTH(ENAME)-2)
--------------------------------------------------------------------------------
JONES
NES

MARTIN
TIN

BLAKE
AKE


ENAME
--------------------
SUBSTR(ENAME,LENGTH(ENAME)-2)
--------------------------------------------------------------------------------
CLARK
ARK

SCOTT
OTT

KING
ING


ENAME
--------------------
SUBSTR(ENAME,LENGTH(ENAME)-2)
--------------------------------------------------------------------------------
TURNER
NER

ADAMS
AMS

JAMES
MES


ENAME
--------------------
SUBSTR(ENAME,LENGTH(ENAME)-2)
--------------------------------------------------------------------------------
FORD
ORD

MILLER
LER


14 rows selected.

PPAS

scott=# select ename,SUBSTR(ename,LENGTH(ename)-2) from emp;
 ename  | substr 
--------+--------
 SMITH  | ITH
 ALLEN  | LEN
 WARD   | ARD
 JONES  | NES
 MARTIN | TIN
 BLAKE  | AKE
 CLARK  | ARK
 SCOTT  | OTT
 KING   | ING
 TURNER | NER
 ADAMS  | AMS
 JAMES  | MES
 FORD   | ORD
 MILLER | LER
(14 rows)

9在SUBSTR()函式中設定負數擷取（-3表示從倒數第三個開始擷取）
Oracle

SQL> select ename,SUBSTR(ename,-3) from emp;

ENAME             SUBSTR(ENAME,-3)
-------------------- ------------------------
SMITH             ITH
ALLEN             LEN
WARD             ARD
JONES             NES
MARTIN             TIN
BLAKE             AKE
CLARK             ARK
SCOTT             OTT
KING             ING
TURNER             NER
ADAMS             AMS

ENAME             SUBSTR(ENAME,-3)
-------------------- ------------------------
JAMES             MES
FORD             ORD
MILLER             LER

14 rows selected.

PPAS

scott=# select ename,SUBSTR(ename,-3) from emp;
 ename  | substr 
--------+--------
 SMITH  | ITH
 ALLEN  | LEN
 WARD   | ARD
 JONES  | NES
 MARTIN | TIN
 BLAKE  | AKE
 CLARK  | ARK
 SCOTT  | OTT
 KING   | ING
 TURNER | NER
 ADAMS  | AMS
 JAMES  | MES
 FORD   | ORD
 MILLER | LER
(14 rows)

10下標從0開始（由於Oracle資料庫設計的靈活性，所有其下標是從0或1開始都是一樣的）
Oracle

SQL> select ename,SUBSTR(ename,0,3) from emp;

ENAME             SUBSTR(ENAME,0,3)
-------------------- ------------------------
SMITH             SMI
ALLEN             ALL
WARD             WAR
JONES             JON
MARTIN             MAR
BLAKE             BLA
CLARK             CLA
SCOTT             SCO
KING             KIN
TURNER             TUR
ADAMS             ADA

ENAME             SUBSTR(ENAME,0,3)
-------------------- ------------------------
JAMES             JAM
FORD             FOR
MILLER             MIL

14 rows selected.

PPAS

scott=# select ename,SUBSTR(ename,0,3) from emp;
 ename  | substr 
--------+--------
 SMITH  | SMI
 ALLEN  | ALL
 WARD   | WAR
 JONES  | JON
 MARTIN | MAR
 BLAKE  | BLA
 CLARK  | CLA
 SCOTT  | SCO
 KING   | KIN
 TURNER | TUR
 ADAMS  | ADA
 JAMES  | JAM
 FORD   | FOR
 MILLER | MIL
(14 rows)

11下標從1開始
Oracle

SQL> select ename,SUBSTR(ename,1,3) from emp;

ENAME             SUBSTR(ENAME,1,3)
-------------------- ------------------------
SMITH             SMI
ALLEN             ALL
WARD             WAR
JONES             JON
MARTIN             MAR
BLAKE             BLA
CLARK             CLA
SCOTT             SCO
KING             KIN
TURNER             TUR
ADAMS             ADA

ENAME             SUBSTR(ENAME,1,3)
-------------------- ------------------------
JAMES             JAM
FORD             FOR
MILLER             MIL

14 rows selected.

PPAS

scott=# select ename,SUBSTR(ename,1,3) from emp;
 ename  | substr 
--------+--------
 SMITH  | SMI
 ALLEN  | ALL
 WARD   | WAR
 JONES  | JON
 MARTIN | MAR
 BLAKE  | BLA
 CLARK  | CLA
 SCOTT  | SCO
 KING   | KIN
 TURNER | TUR
 ADAMS  | ADA
 JAMES  | JAM
 FORD   | FOR
 MILLER | MIL
(14 rows)

12返回指定字元的ASCII碼
Oracle

SQL> select ASCII(`L`) from dual;

ASCII(`L`)
----------
    76

PPAS

scott=# select ASCII(`L`) from dual;
 ascii 
-------
    76
(1 row)

13驗證CHR()函式，將ASCII碼變回字元
Oracle

SQL> select CHR(100) from dual;

CH
--
d

PPAS

scott=# select CHR(100) from dual;
 chr 
-----
 d
(1 row)

14去掉字串左邊空格函式—LTRIM()
Oracle

SQL> select `     SongYuejie Vastdata     ` 原始字串,LTRIM(`     SongYuejie Vastdata     `) 去掉左空格 from dual ;

原始字串
----------------------------------------------------------
去掉左空格
------------------------------------------------
     SongYuejie Vastdata
SongYuejie Vastdata

PPAS

scott=# select `     SongYuejie Vastdata     ` 原始字串,LTRIM(`     SongYuejie Vastdata     `) 去掉左空格 from dual ;
          原始字串           |        去掉左空格        
-------------------------------+--------------------------
      SongYuejie Vastdata      | SongYuejie Vastdata     
(1 row)

15去掉字串右邊空格函式—RTRIM()
Oralce

SQL> select `     SongYuejie Vastdata     ` 原始字串,RTRIM(`     SongYuejie Vastdata     `) 去掉右空格 from dual ;

原始字串
----------------------------------------------------------
去掉右空格
------------------------------------------------
     SongYuejie Vastdata
     SongYuejie Vastdata

PPAS

scott=# select `     SongYuejie Vastdata     ` 原始字串,RTRIM(`     SongYuejie Vastdata     `) 去掉右空格 from dual ;  
          原始字串           |        去掉右空格        
-------------------------------+--------------------------
      SongYuejie Vastdata      |      SongYuejie Vastdata
(1 row)

16去掉左右兩邊空格函式—TRIM()
Oracle

SQL> select `     SongYuejie Vastdata     ` 原始字串,TRIM(`     SongYuejie Vastdata     `) 去掉右空格 from dual ; 

原始字串
----------------------------------------------------------
去掉右空格
--------------------------------------
     SongYuejie Vastdata
SongYuejie Vastdata

PPAS

scott=# select `     SongYuejie Vastdata     ` 原始字串,TRIM(`     SongYuejie Vastdata     `) 去掉右空格 from dual ; 
          原始字串           |     去掉右空格      
-------------------------------+---------------------
         SongYuejie Vastdata      | SongYuejie Vastdata
(1 row)

17字串左、右填充函式—LPAD()、RPAD()
Oracle

SQL> select LPAD(`Vastdata`,10,`*`) LPAD函式使用,RPAD(`Vastdata`,10,`*`) RPAD函式使用,
  2  LPAD(RPAD(`Vastdata`,10,`*`),16,`*`) 組合使用 from dual;

LPAD函式使用         RPAD函式使用      組合使用
-------------------- -------------------- --------------------------------
**Vastdata         Vastdata**       ******Vastdata**

PPAS

scott=#  select LPAD(`Vastdata`,10,`*`) LPAD函式使用,RPAD(`Vastdata`,10,`*`) RPAD函式使用, 
scott-# LPAD(RPAD(`Vastdata`,10,`*`),16,`*`) 組合使用 from dual;
 lpad函式使用 | rpad函式使用 |     組合使用     
--------------+--------------+------------------
 **Vastdata   | Vastdata**   | ******Vastdata**
(1 row)

18字串查詢函式—INSTR()
Oracle

SQL> select INSTR(`SongYuejie Vastdata`,`Vastdata`)查詢得到,        
  2  INSTR(`SongYuejie Vastdata`,`SongYuejie`)查詢得到,
  3  INSTR(`SongYuejie Vastdata`,`VASTDATA`)查詢不到
  4  from dual;

  查詢得到   查詢得到    查詢不到
---------- ---------- ----------
    12        1           0

PPAS

scott=# select INSTR(`SongYuejie Vastdata`,`Vastdata`)查詢得到,                 
scott-# INSTR(`SongYuejie Vastdata`,`SongYuejie`)查詢得到,
scott-# INSTR(`SongYuejie Vastdata`,`VASTDATA`)查詢不到
scott-# from dual;
 查詢得到 | 查詢得到 | 查詢不到 
----------+----------+----------
       12 |        1 |        0
(1 row)

數值函式
19驗證ROUND()函式的使用
Oracle

SQL> select ROUND(789.652) 不保留小數,ROUND(789.652,2) 保留兩位小數,ROUND(789.652,-1) 處理整數進位 from dual;

不保留小數 保留兩位小數 處理整數進位
---------- ------------ ------------
       790     789.65      790

PPAS

scott=# select ROUND(789.652) 不保留小數,ROUND(789.652,2) 保留兩位小數,ROUND(789.652,-1) 處理整數進位 from dual;
 不保留小數 | 保留兩位小數 | 處理整數進位 
------------+--------------+--------------
        790 |       789.65 |          790
(1 row)

20列出每個僱員的一些基本資訊和日工資情況
Oracle

SQL> select empno,ename,job,hiredate,sal,ROUND(sal/30,2) 日薪金 from emp;

     EMPNO ENAME        JOB           HIREDATE           SAL
---------- -------------------- ------------------ ------------ ----------
    日薪金
----------
      7369 SMITH        CLERK           17-DEC-80           800
     26.67

      7499 ALLEN        SALESMAN       20-FEB-81          1600
     53.33

      7521 WARD         SALESMAN       22-FEB-81          1250
     41.67


     EMPNO ENAME        JOB           HIREDATE           SAL
---------- -------------------- ------------------ ------------ ----------
    日薪金
----------
      7566 JONES        MANAGER        02-APR-81          2975
     99.17

      7654 MARTIN        SALESMAN       28-SEP-81          1250
     41.67

      7698 BLAKE        MANAGER        01-MAY-81          2850
    95


     EMPNO ENAME        JOB           HIREDATE           SAL
---------- -------------------- ------------------ ------------ ----------
    日薪金
----------
      7782 CLARK        MANAGER        09-JUN-81          2450
     81.67

      7788 SCOTT        ANALYST        19-APR-87          3000
       100

      7839 KING         PRESIDENT       17-NOV-81          5000
    166.67


     EMPNO ENAME        JOB           HIREDATE           SAL
---------- -------------------- ------------------ ------------ ----------
    日薪金
----------
      7844 TURNER        SALESMAN       08-SEP-81          1500
    50

      7876 ADAMS        CLERK           23-MAY-87          1100
     36.67

      7900 JAMES        CLERK           03-DEC-81           950
     31.67


     EMPNO ENAME        JOB           HIREDATE           SAL
---------- -------------------- ------------------ ------------ ----------
    日薪金
----------
      7902 FORD         ANALYST        03-DEC-81          3000
       100

      7934 MILLER        CLERK           23-JAN-82          1300
     43.33


14 rows selected.

PPAS

scott=# select empno,ename,job,hiredate,sal,ROUND(sal/30,2) 日薪金 from emp;
 empno | ename  |    job    |      hiredate      |   sal   | 日薪金 
-------+--------+-----------+--------------------+---------+--------
  7369 | SMITH  | CLERK     | 17-DEC-80 00:00:00 |  800.00 |  26.67
  7499 | ALLEN  | SALESMAN  | 20-FEB-81 00:00:00 | 1600.00 |  53.33
  7521 | WARD   | SALESMAN  | 22-FEB-81 00:00:00 | 1250.00 |  41.67
  7566 | JONES  | MANAGER   | 02-APR-81 00:00:00 | 2975.00 |  99.17
  7654 | MARTIN | SALESMAN  | 28-SEP-81 00:00:00 | 1250.00 |  41.67
  7698 | BLAKE  | MANAGER   | 01-MAY-81 00:00:00 | 2850.00 |  95.00
  7782 | CLARK  | MANAGER   | 09-JUN-81 00:00:00 | 2450.00 |  81.67
  7788 | SCOTT  | ANALYST   | 19-APR-87 00:00:00 | 3000.00 | 100.00
  7839 | KING   | PRESIDENT | 17-NOV-81 00:00:00 | 5000.00 | 166.67
  7844 | TURNER | SALESMAN  | 08-SEP-81 00:00:00 | 1500.00 |  50.00
  7876 | ADAMS  | CLERK     | 23-MAY-87 00:00:00 | 1100.00 |  36.67
  7900 | JAMES  | CLERK     | 03-DEC-81 00:00:00 |  950.00 |  31.67
  7902 | FORD   | ANALYST   | 03-DEC-81 00:00:00 | 3000.00 | 100.00
  7934 | MILLER | CLERK     | 23-JAN-82 00:00:00 | 1300.00 |  43.33
(14 rows)

本連載部落格主要探討Oracle與PPAS(PostgreSQL)資料庫的差異，以幫助更多讀者瞭解如何實現資料庫遷移！

《卸甲筆記》-單行函式對比之三
2016-06-22
筆記函式
《卸甲筆記》-分組統計查詢對比
2016-06-27
筆記
ORACLE單行函式與多行函式之一
2013-11-02
Oracle函式
《卸甲筆記》-PostgreSQL和Oracle的SQL差異分析之一：外連線
2016-07-26
筆記SQLOracle
《卸甲筆記》-PostgreSQL和Oracle的資料型別的對比系列五：其它型別
2016-07-15
筆記SQLOracle資料型別
《卸甲筆記》-PostgreSQL和Oracle的SQL差異分析之五：函式的差異(五)
2016-08-18
筆記SQLOracle函式
《卸甲筆記》-PostgreSQL和Oracle的SQL差異分析之五：函式的差異(三)
2016-08-12
筆記SQLOracle函式
《卸甲筆記》-PostgreSQL和Oracle的SQL差異分析之五：函式的差異(一)
2016-08-08
筆記SQLOracle函式
《卸甲筆記》-PostgreSQL和Oracle的資料型別的對比系列四：大資料型別
2016-07-13
筆記SQLOracle資料型別大資料
時間函式對比
2014-08-11
函式
學習筆記:11種行為型設計模式簡單對比
2009-10-13
筆記設計模式
SQL菜鳥筆記之第九篇 SQL單行函式（下）
2011-08-31
SQL筆記函式
SQL菜鳥筆記之第八篇 SQL單行函式（中）
2011-08-31
SQL筆記函式
MySQL和Oracle行值表示式對比(r11筆記第74天)
2017-02-13
MySqlOracle筆記
ES6函式比對ES5函式
2019-02-28
函式
常見函式之單行函式
2020-07-04
函式
SQL菜鳥筆記之第七篇 SQL單行函式（上）
2011-08-31
SQL筆記函式
intrans函式（對比度拉伸）
2018-12-05
函式
SQLServer和Oracle常用函式對比
2007-08-28
SQLServerOracle函式
javascript 的函式宣告與表示式對比
2020-10-10
JavaScript函式
python筆記-函式
2021-09-09
Python筆記函式
筆記：Python函式
2018-10-01
筆記Python函式
C++筆記--函式
2016-05-25
C++筆記函式
【筆記】普通生成函式
2024-03-25
筆記函式
對beego的控制器函式進行單測
2019-02-16
Go函式
SQLServer和Oracle的常用函式對比
2010-11-30
SQLServerOracle函式
JavaScript 匿名函式與具名函式執行效率比較
2019-06-05
JavaScript函式
js匿名函式和具名函式執行效率比較
2017-03-29
JS函式
async函式學習筆記。
2019-02-16
函式筆記
substr擷取函式筆記
2020-12-03
函式筆記
Go 函式學習筆記
2017-08-12
Go函式筆記
函式-PHP手冊筆記
2015-06-19
函式PHP筆記
mysql常用函式--個人筆記
2015-07-29
MySql函式筆記
【筆記】oracle 統計函式
2009-03-13
筆記Oracle函式
分析函式（學習筆記）
2007-05-02
函式筆記
PHP筆記：建構函式與解構函式
2021-09-09
PHP筆記函式
ORACLE單行函式與多行函式之二：字元函式示例
2013-11-02
Oracle函式字元
ORACLE單行函式與多行函式之三：數值函式
2013-11-02
Oracle函式

《卸甲筆記》-單行函式對比之一

相關文章