《卸甲笔记》-PostgreSQL和Oracle的SQL差异分析之五:函数的差异(五)-阿里云开发者社区

开发者社区> 瀚高大李> 正文

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

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
差异分析①
加载数据 setwd("D:\\diff") # Reading in count data files
1092 0
Oracle字符乱码、数据越界访问典型Bug分析
作为乙方,在甲方客户那里验收阶段发现两个诡异Bug。以下就问题来源、问题根因、解决方案、如何避免做详细描述。
337 0
理解和使用Oracle分析工具LogMiner
用LogMiner             理解和使用Oracle   8i分析工具LogMiner                 Oracle   LogMiner   是Oracle公司从产品8i以后提供的一个实际非常有用的分析工具,使用该工具可以轻松获得Oracle   重作日志文件(归档日志文件)中的具体内容,特别是,该工具可以分析出所有对于数据库操作的DML(insert、update、delete等)语句,另外还可分析得到一些必要的回滚SQL语句。
1174 0
PostGIS学习笔记(开篇)
PostGIS事实上算是笔者开始写博客的第一篇内容。而事实上那篇博文的内容并不丰富,笔者对PostGIS的了解仍然不多,然而17年在OSGeo课程学习时对PostGIS又有了进一步了解,并逐步发现它的强大。刚好最近又遇上一个问题,也使我萌发了开坑PostGIS的想法。
1548 0
【SQL 学习】分析函数之RANK() DENSE_RANK ()
SQL> --RANK DENSE_RANK 函数SQL> select prd_type_id,sum(amount),  2  rank() over (order by sum(amount) desc) as rank,  3  dense_rank()...
596 0
差异分析②
样品的无监督聚类 检查基因表达分析最重要的探索性策略之一是多维定标(MDS)图或类似的图。该图以无监督的方式显示了样本之间的相似性和不相似性,以便人们可以了解在进行正式测试之前可以检测差异表达的程度。
869 0
+关注
16
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载