《卸甲笔记》-PostgreSQL和Oracle的SQL差异分析之五：函数的差异(三)

2022-03-10 00:21:30

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 行记录)

码农公寓