《卸甲筆記》-PostgreSQL和Oracle的SQL差異分析之五：函式的差異(五)

PostgreSQL是世界上功能最強大的開源資料庫，在國內得到了越來越多機構和開發者的青睞和應用。隨著PostgreSQL的應用越來越廣泛，Oracle向PostgreSQL資料庫的資料遷移需求也越來越多。資料庫之間資料遷移的時候，首先是遷移資料，然後就是SQL、儲存過程、序列等程式中不同的資料庫中資料的使用方式的轉換。下面根據自己的理解和測試，寫了一些SQL以及資料庫物件轉換方面的文章，不足之處，尚請多多指教。

1、regexp_replace

regexp_replace是使用正規表示式進行替換的函式。源字串裡面符合正規表示式的，替換成目標字串。Oracle和PostgreSQL都支援regexp_replace，但是引數有些不同。

Oracle的regexp_replace共有六個引數。分別表示源字串，正規表示式，目標字串，開始位置(預設為1)，替換第幾個匹配（預設全部）和一個flg值（`i`：大小寫不敏感 ‘c’：大小寫敏感 `n`：點號，不匹配換行符號 `m‘：多行模式 `x`：擴充套件模式，忽略正規表示式中的空白字元）。

PostgreSQL中的regexp_replace只有四個引數，分別表示源字串，正規表示式，目標字串，替換規則(預設只替換第一個。當替換規則為`g`的時候代表全部替換)。

PostgreSQL中的regexp_replace的功能沒有Oracle的regexp_replace的功能強。有些比較複雜的替換，只能自己通過函式實現。

Oracle regexp_replace

SQL> select regexp_replace(`abc123xyz888`, `[0-9]+`) from  dual;

REGEXP
------
abcxyz

SQL> select regexp_replace(`abc123xyz888`, `[0-9]+`, `ZZZ`) from  dual;

REGEXP_REPLA
------------
abcZZZxyzZZZ

SQL> select regexp_replace(`abc123xyz888`, `[0-9]+`, `ZZZ`, 8) from  dual;

REGEXP_REPLA
------------
abc123xyzZZZ

SQL> select regexp_replace(`abc123xyz888`, `[0-9]+`, `ZZZ`, 1, 2) from  dual;

REGEXP_REPLA
------------
abc123xyzZZZ

SQL> select regexp_replace(`abc123xyz888`, `[a-c]+`, `ZZZ`, 1, 1, `i`)  from  dual;

REGEXP_REPLA
------------
ZZZ123xyz888

SQL> select regexp_replace(`abc123xyz888`, `[A-C]+`, `ZZZ`, 1, 1, `i`)  from  dual;

REGEXP_REPLA
------------
ZZZ123xyz888

SQL> select regexp_replace(`abc123xyz888`, `[A-C]+`, `ZZZ`, 1, 1, `c`)  from  dual;

REGEXP_REPLA
------------
abc123xyz888

PostgreSQL regexp_replace

postgres=# select regexp_replace(`abc123xyz888`, `[0-9]+`) ;
錯誤:  函式 regexp_replace(unknown, unknown) 不存在
第1行select regexp_replace(`abc123xyz888`, `[0-9]+`) ;
            ^
提示:  沒有匹配指定名稱和引數型別的函式. 您也許需要增加明確的型別轉換.
postgres=#  select regexp_replace(`abc123xyz888`, `[0-9]+`, `ZZZ`);
 regexp_replace
----------------
 abcZZZxyz888
(1 行記錄)

postgres=#  select regexp_replace(`abc123xyz888`, `[0-9]+`, `ZZZ`, `g`);
 regexp_replace
----------------
 abcZZZxyzZZZ
(1 行記錄)

2、regexp_substr

regexp_substr是Oracle的使用正規表示式進行字串擷取的函式。PostgreSQL中的substring函式中可以實現它的簡單功能，但是沒有Oracle的功能強。如果需要實現它的複雜功能，需要自己使用函式實現。
Oracle的regexp_substr函式有五個引數。分別表示源字串，正規表示式，開始位置(預設為1)，擷取第幾個匹配（預設第一個）和一個flg值（`i`：大小寫不敏感 ‘c’：大小寫敏感 `n`：點號，不匹配換行符號 `m‘：多行模式 `x`：擴充套件模式，忽略正規表示式中的空白字元）。
PostgreSQL中的regexp_substr只有三個引數，分別表示源字串，正規表示式，逃匿符。

Oracle regexp_substr

SQL> select regexp_substr(`abc123abc888`, `[a-c]+`)  from  dual;

REG
---
abc

SQL> select regexp_substr(`abc123abc888`, `[a-c]+`, 2)  from  dual;

RE
--
bc

SQL> select regexp_substr(`abc123abc888`, `[a-c]+`, 5)  from  dual;

REG
---
abc

SQL> select regexp_substr(`abc123abc888`, `[a-c]+`, 1, 2)  from  dual;

REG
---
abc

SQL> select regexp_substr(`abc123abc888`, `[A-C]+`, 1, 1)  from  dual;

R
-

SQL> select regexp_substr(`abc123abc888`, `[A-C]+`, 1, 1, `i`)  from  dual;

REG
---
abc

SQL> select regexp_substr(`abc123abc888`, `[A-C]+`, 1, 1, `c`)  from  dual;

R
-

PostgreSQL substring

postgres=# select substring(`abc123abc888`, `[a-c]+`) ;
 substring
-----------
 abc
(1 行記錄)

postgres=# select substring(`abc123abc888` from  `[a-c]+`) ;
 substring
-----------
 abc
(1 行記錄)

postgres=# select substring(`Thomas` , `%#"o_a#"%` ,`#`);
 substring
-----------
 oma
(1 行記錄)

postgres=# select substring(`Thomas` from `%#"o_a#"%` for `#`);
 substring
-----------
 oma
(1 行記錄)

3、regexp_instr

regexp_instr是Oracle的一個搜尋字串中符合表示式的字串位置的一個函式。在PostgreSQL中沒有對應的函式，需要自己寫函式實現它。
最簡單的功能，可以使用position函式和substring函式來模擬實現。

Oracle regexp_instr

SQL> select regexp_instr(`abc123abc156`, `[1-2]+`)  from  dual;

REGEXP_INSTR(`ABC123ABC156`,`[1-2]+`)
-------------------------------------
                                    4

SQL> select regexp_instr(`abc123abc156`, `[1-2]+`, 7)  from  dual;

REGEXP_INSTR(`ABC123ABC156`,`[1-2]+`,7)
---------------------------------------
                                     10

SQL> select regexp_instr(`abc123abc156`, `[1-2]+`, 1, 2)  from  dual;

REGEXP_INSTR(`ABC123ABC156`,`[1-2]+`,1,2)
-----------------------------------------
                                       10

SQL> select regexp_instr(`abc123abc156`, `[1-2]+`, 1, 1, 0)  from  dual;

REGEXP_INSTR(`ABC123ABC156`,`[1-2]+`,1,1,0)
-------------------------------------------
                                          4

SQL> select regexp_instr(`abc123abc156`, `[1-2]+`, 1, 1, 1)  from  dual;

REGEXP_INSTR(`ABC123ABC156`,`[1-2]+`,1,1,1)
-------------------------------------------
                                          6

SQL> select regexp_instr(`abc123abc156`, `[A-C]+`, 1, 2, 0, `i`)  from  dual;

REGEXP_INSTR(`ABC123ABC156`,`[A-C]+`,1,2,0,`I`)
-----------------------------------------------
                                              7

SQL> select regexp_instr(`abc123abc156`, `[A-C]+`, 1, 2, 0, `c`)  from  dual;

REGEXP_INSTR(`ABC123ABC156`,`[A-C]+`,1,2,0,`C`)
-----------------------------------------------
                                              0

PostgreSQL position substring

postgres=# select position(substring(`abc123abc156` from `[1-2]+`) in `abc123abc156`);
 position
----------
        4
(1 行記錄)


postgres=# select 7 - 1 + position(substring(substring(`abc123AC158`,7) from `[1-2]+`) in `abc123AC158`);
 ?column?
----------
       10
(1 行記錄)

4、regexp_like

regexp_like是Oracle的一個檢索正規表示式條件的一個函式，只能用在條件判斷部分。在PostgreSQL中沒有對應的函式，需要自己寫函式實現它。
最簡單的功能，可以簡單的使用substring和length函式來模擬。

Oracle regexp_like

SQL> select * from o_test;

        ID NAME       AGE
---------- ---------- ----------
         1 James1234  20
         2 De Haan    20
         2 Greenberg  20
         2 TomLiu     20

SQL> select * from o_test where regexp_like(name, `([aeiou])1`);

        ID NAME       AGE
---------- ---------- ----------
         2 De Haan    20
         2 Greenberg  20

SQL> select * from o_test where regexp_like(name, `([AEIOU])1`);

未選定行

SQL> select * from o_test where regexp_like(name, `([AEIOU])1`, `i`);

        ID NAME       AGE
---------- ---------- ----------
         2 De Haan    20
         2 Greenberg  20

SQL> select * from o_test where regexp_like(name, `([AEIOU])1`, `c`);

未選定行

PostgreSQL length substring

postgres=# select * from p_test where length(substring(name, `([aeiou])1`)) > 0;
 id |   name    | age
----+-----------+-----
  2 | De Haan   | 20
  3 | Greenberg | 20
(2 行記錄)

postgres=# select * from p_test where length(substring(name, `([AEIOU])1`)) > 0;
 id | name | age
----+------+-----
(0 行記錄)

postgres=# select * from p_test where length(substring(name, `([AEIOUaeiou])1`)) > 0;
 id |   name    | age
----+-----------+-----
  2 | De Haan   | 20
  3 | Greenberg | 20
(2 行記錄)

參考文件：

PostgreSQL 9.4.4 中文手冊：
http://www.postgres.cn/docs/9.4/functions-string.html (字串函式和操作符）
Database SQL Language Reference：
http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions148.htm#SQLRF06300
http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions149.htm#SQLRF06302
http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions150.htm#SQLRF06303
http://docs.oracle.com/cd/E11882_01/server.112/e41084/conditions007.htm#SQLRF00501