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

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

1、substr

substr函式是擷取一個字串的一部分，格式是substr(字串，開始位置，長度)。Oracle和PostgreSQL都支援這個函式。但是具體使用的時候，有些不一樣的地方。
Oracle中，substr是一系列函式。還包括substrb, substrc, substr2, substr4等多個。分別是按照字元(substr)擷取、按位元組(substrb)擷取、按Unicode碼(substrc)擷取、按照UCS2編碼(substr2)擷取和按照UCS4碼(substr4)擷取。這幾個函式的用法是一致的。
PostgreSQL中，substr方法只支援字元方式。不支援其餘的方式。引數和Oracle是一樣的。而且還有一個叫substring方法，和substr方法是一樣的。
Oracle和PostgreSQL中的用法，區別就是當開始位置=0的時候，Oracle表示的含義和1是一樣的，從第一個字元開始擷取。而PostgreSQL相當於第一個字元再往前一個空字元。當第二個引數<0的時候，Oracle代表開始位置從右側開始算。而PostgreSQL則表示則是從左側望再前追加空字元。另外，PostgreSQL引數還支援使用關鍵字from 和 for的寫法，分別表示開始位置和長度。

Oracle substr

SQL> select substr(`bbb姑蘇城外寒山寺aaa`,4) from  dual;

SUBSTR(`BBB姑蘇城
-----------------
姑蘇城外寒山寺aaa

SQL> select substr(`bbb姑蘇城外寒山寺aaa`,5, 5) from  dual;

SUBSTR(`BB
----------
蘇城外寒山

SQL> select substrb(`bbb姑蘇城外寒山寺aaa`,5, 5) from  dual;

SUBST
-----
 蘇城

SQL> select substrc(`bbb姑蘇城外寒山寺aaa`,5, 5) from  dual;

SUBSTRC(`B
----------
蘇城外寒山

SQL> select substr2(`bbb姑蘇城外寒山寺aaa`,5, 5) from  dual;

SUBSTR2(`B
----------
蘇城外寒山

SQL> select substr4(`bbb姑蘇城外寒山寺aaa`,5, 5) from  dual;

SUBSTR4(`B
----------
蘇城外寒山

SQL> select substr(`bbb姑蘇城外寒山寺aaa`,-8, 5) from  dual;

SUBSTR(`BB
----------
城外寒山寺

SQL> select substr(`bbb姑蘇城外寒山寺aaa`,0, 5) from  dual;

SUBSTR(
-------
bbb姑蘇

PostgreSQL substr

postgres=# select substr(`bbb姑蘇城外寒山寺aaa`,4);
      substr
-------------------
 姑蘇城外寒山寺aaa
(1 行記錄)

postgres=# select substr(`bbb姑蘇城外寒山寺aaa`,5, 5);
   substr
------------
 蘇城外寒山
(1 行記錄)

postgres=# select substr(`bbb姑蘇城外寒山寺aaa`,0, 10);
     substr
-----------------
 bbb姑蘇城外寒山
(1 行記錄)

postgres=# select substr(`bbb姑蘇城外寒山寺aaa`,-5, 10);
 substr
--------
 bbb姑
(1 行記錄)

postgres=# select substring(`bbb姑蘇城外寒山寺aaa` from 4);
     substring
-------------------
 姑蘇城外寒山寺aaa
(1 行記錄)

postgres=# select substring(`bbb姑蘇城外寒山寺aaa` from 5 for 5);
 substring
------------
 蘇城外寒山
(1 行記錄)

postgres=# select substring(`bbb姑蘇城外寒山寺aaa` from 0 for 10);
    substring
-----------------
 bbb姑蘇城外寒山
(1 行記錄)

postgres=# select substring(`bbb姑蘇城外寒山寺aaa` from -5 for 10);
 substring
-----------
 bbb姑
(1 行記錄)

2、length

length(字串)函式是求得字串的長度。Oracle和PostgreSQL都支援這個函式。
Oracle中，length是一系列函式。還包括lengthb, lengthc, length2, length4等多個。分別是按照字元(length)取長度、按位元組(lengthb)取長度、按Unicode碼(lengthc)取長度、按照UCS2編碼(length2)取長度和按照UCS4碼(length4)取長度。這幾個函式的用法是一致的。
PostgreSQL中，length方法只支援字元方式， 不支援其餘的方式。引數和Oracle是一樣的。
遷移的時候，按照字元以外的方式取長度，PostgreSQL還不支援。

Oracle length

SQL> select length(`bbb姑蘇城外寒山寺aaa`) from  dual;

LENGTH(`BBB姑蘇城外寒山寺AAA`)
------------------------------
                            13

SQL> select lengthb(`bbb姑蘇城外寒山寺aaa`) from  dual;

LENGTHB(`BBB姑蘇城外寒山寺AAA`)
-------------------------------
                             20

PostgreSQL length

postgres=# select length(`bbb姑蘇城外寒山寺aaa`);
 length
--------
     13
(1 行記錄)


postgres=# select lengthb(`bbb姑蘇城外寒山寺aaa`);
錯誤:  函式 lengthb(unknown) 不存在
第1行select lengthb(`bbb姑蘇城外寒山寺aaa`);
            ^
提示:  沒有匹配指定名稱和引數型別的函式. 您也許需要增加明確的型別轉換.

3、trim/ltrim/rtrim函式

trim函式用來除去字串開頭和結尾的指定字元（預設是空格）。ltrim可以除去左側開頭的指定字元，rtrim除去右側開頭的指定字元。

Oracle的trim只能除去一個特定字元。兩個以上不支援。改變字元的時候，使用關鍵字from。比如trim(`a` from `aabbccaa`)這種寫法。不支援trim(`aabbcc`, `a`)的這種寫法。但是ltrim和rtrim支援多個字元。並且支援ltrim(`aabbcc`, `a`)和rtrim(`aabbcc`, `c`)這種寫法，而不支援from關鍵字的寫法。另外，trim的from關鍵字的寫法中還支援Both/ leading/ trailing三個關鍵字，分別代表除去 開頭和結尾 / 開頭 / 結尾的指定字元。

PostgreSQL中，都支援除去開始結尾的多個字元。並且PostgreSQL中， trim的兩種寫法都是支援的，而ltrim和rtrim和oracle一樣, 支援ltrim(`aabbcc`, `a`)和rtrim(`aabbcc`, `c`)這種寫法，而不支援from關鍵字的寫法。PostgreSQL的trim的from關鍵字的寫法也支援Both/ leading/ trailing三個關鍵字，分別代表除去 開頭和結尾 / 開頭 / 結尾的指定字元。

遷移的時候，可以直接遷移。

Oracle trim

SQL> select trim(` aa ` ) from dual;

TR
--
aa

SQL> select ltrim(` aa `) from dual;

LTR
---
aa

SQL> select rtrim(` aa `) from dual;

RTR
---
 aa

SQL> select trim(`aabbccaa`, `a`) from dual;
select trim(`aabbccaa`, `a`) from dual
                      *
第 1 行出現錯誤:
ORA-00907: 缺失右括號


SQL> select trim(`a` from `aabbccaa`) from dual;

TRIM
----
bbcc

SQL> select trim(`ab` from `aabbccaa`) from dual;
select trim(`ab` from `aabbccaa`) from dual
       *
第 1 行出現錯誤:
ORA-30001: 擷取集僅能有一個字元

SQL> select ltrim(`ab` from `aabbccaa`) from dual;
select ltrim(`ab` from `aabbccaa`) from dual
                  *
第 1 行出現錯誤:
ORA-00907: 缺失右括號

SQL> select ltrim(`aabbccaa`,`ab`) from dual;

LTRI
----
ccaa

SQL> select rtrim(`aabbccaa`,`ab`) from dual;

RTRIM(
------
aabbcc

SQL> select trim(both `a` from  `abcdefa`)  from dual;

TRIM(
-----
bcdef

SQL> select trim(leading `a` from  `abcdefa`)  from dual;

TRIM(L
------
bcdefa

SQL> select trim(trailing `a` from  `abcdefa`)  from dual;

TRIM(T
------
abcdef

PostgreSQL trim

postgres=# select trim(` aa `) aa ;
 aa
----
 aa
(1 行記錄)


postgres=# select ltrim(` aa `) aa ;
 aa
-----
 aa
(1 行記錄)


postgres=# select rtrim(` aa `) aa ;
 aa
-----
  aa
(1 行記錄)

postgres=# select trim(`aabbccaa`, `a`) aa;
  aa
------
 bbcc
(1 行記錄)

postgres=# select trim(`ab` from `aabbccaa`) aa;
 aa
----
 cc
(1 行記錄)

postgres=# select ltrim(`ab` from `aabbccaa`) aa;
錯誤:  語法錯誤 在 "from" 或附近的
第1行select ltrim(`ab` from `aabbccaa`) aa;
                       ^
postgres=# select ltrim(`aabbccaa`,`ab`) ;
 ltrim
-------
 ccaa
(1 行記錄)


postgres=# select rtrim(`aabbccaa`,`ab`) ;
 rtrim
--------
 aabbcc
(1 行記錄)


postgres=#  select trim(both `a` from  `abcdefa`)  ;
 btrim
-------
 bcdef
(1 行記錄)


postgres=#  select trim(leading `a` from  `abcdefa`)  ;
 ltrim
--------
 bcdefa
(1 行記錄)


postgres=#  select trim(trailing `a` from  `abcdefa`)  ;
 rtrim
--------
 abcdef
(1 行記錄)