《卸甲笔记》-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

上一篇:Oracle缩写


下一篇:IntelliJ IDEA 2017 配置Tomcat 运行Web项目