本文共 6398 字，大约阅读时间需要 21 分钟。
PostgreSQL是世界上功能最强大的开源数据库，在国内得到了越来越多机构和开发者的青睐和应用。随着PostgreSQL的应用越来越广泛，Oracle向PostgreSQL数据库的数据迁移需求也越来越多。数据库之间数据迁移的时候，首先是迁移数据，然后就是SQL、存储过程、序列等程序中不同的数据库中数据的使用方式的转换。下面根据自己的理解和测试，写了一些SQL以及数据库对象转换方面的文章，不足之处，尚请多多指教。
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的功能强。有些比较复杂的替换，只能自己通过函数实现。
SQL> select regexp_replace('abc123xyz888', '[0-9]+') from dual;REGEXP------abcxyzSQL> select regexp_replace('abc123xyz888', '[0-9]+', 'ZZZ') from dual;REGEXP_REPLA------------abcZZZxyzZZZSQL> select regexp_replace('abc123xyz888', '[0-9]+', 'ZZZ', 8) from dual;REGEXP_REPLA------------abc123xyzZZZSQL> select regexp_replace('abc123xyz888', '[0-9]+', 'ZZZ', 1, 2) from dual;REGEXP_REPLA------------abc123xyzZZZSQL> select regexp_replace('abc123xyz888', '[a-c]+', 'ZZZ', 1, 1, 'i') from dual;REGEXP_REPLA------------ZZZ123xyz888SQL> select regexp_replace('abc123xyz888', '[A-C]+', 'ZZZ', 1, 1, 'i') from dual;REGEXP_REPLA------------ZZZ123xyz888SQL> select regexp_replace('abc123xyz888', '[A-C]+', 'ZZZ', 1, 1, 'c') from dual;REGEXP_REPLA------------abc123xyz888 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 行记录) regexp_substr是Oracle的使用正则表达式进行字符串截取的函数。PostgreSQL中的substring函数中可以实现它的简单功能，但是没有Oracle的功能强。如果需要实现它的复杂功能，需要自己使用函数实现。
Oracle的regexp_substr函数有五个参数。分别表示源字符串，正则表达式，开始位置(默认为1)，截取第几个匹配（默认第一个）和一个flg值（'i'：大小写不敏感 ‘c’：大小写敏感 'n'：点号，不匹配换行符号 'm‘：多行模式 'x'：扩展模式，忽略正则表达式中的空白字符）。PostgreSQL中的regexp_substr只有三个参数，分别表示源字符串，正则表达式，逃匿符。SQL> select regexp_substr('abc123abc888', '[a-c]+') from dual;REG---abcSQL> select regexp_substr('abc123abc888', '[a-c]+', 2) from dual;RE--bcSQL> select regexp_substr('abc123abc888', '[a-c]+', 5) from dual;REG---abcSQL> select regexp_substr('abc123abc888', '[a-c]+', 1, 2) from dual;REG---abcSQL> select regexp_substr('abc123abc888', '[A-C]+', 1, 1) from dual;R-SQL> select regexp_substr('abc123abc888', '[A-C]+', 1, 1, 'i') from dual;REG---abcSQL> select regexp_substr('abc123abc888', '[A-C]+', 1, 1, 'c') from dual;R- 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 行记录) regexp_instr是Oracle的一个搜索字符串中符合表达式的字串位置的一个函数。在PostgreSQL中没有对应的函数，需要自己写函数实现它。
最简单的功能，可以使用position函数和substring函数来模拟实现。SQL> select regexp_instr('abc123abc156', '[1-2]+') from dual;REGEXP_INSTR('ABC123ABC156','[1-2]+')------------------------------------- 4SQL> select regexp_instr('abc123abc156', '[1-2]+', 7) from dual;REGEXP_INSTR('ABC123ABC156','[1-2]+',7)--------------------------------------- 10SQL> select regexp_instr('abc123abc156', '[1-2]+', 1, 2) from dual;REGEXP_INSTR('ABC123ABC156','[1-2]+',1,2)----------------------------------------- 10SQL> select regexp_instr('abc123abc156', '[1-2]+', 1, 1, 0) from dual;REGEXP_INSTR('ABC123ABC156','[1-2]+',1,1,0)------------------------------------------- 4SQL> select regexp_instr('abc123abc156', '[1-2]+', 1, 1, 1) from dual;REGEXP_INSTR('ABC123ABC156','[1-2]+',1,1,1)------------------------------------------- 6SQL> select regexp_instr('abc123abc156', '[A-C]+', 1, 2, 0, 'i') from dual;REGEXP_INSTR('ABC123ABC156','[A-C]+',1,2,0,'I')----------------------------------------------- 7SQL> select regexp_instr('abc123abc156', '[A-C]+', 1, 2, 0, 'c') from dual;REGEXP_INSTR('ABC123ABC156','[A-C]+',1,2,0,'C')----------------------------------------------- 0 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 行记录) regexp_like是Oracle的一个检索正则表达式条件的一个函数，只能用在条件判断部分。在PostgreSQL中没有对应的函数，需要自己写函数实现它。
最简单的功能，可以简单的使用substring和length函数来模拟。SQL> select * from o_test; ID NAME AGE---------- ---------- ---------- 1 James1234 20 2 De Haan 20 2 Greenberg 20 2 TomLiu 20SQL> select * from o_test where regexp_like(name, '([aeiou])\1'); ID NAME AGE---------- ---------- ---------- 2 De Haan 20 2 Greenberg 20SQL> 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 20SQL> select * from o_test where regexp_like(name, '([AEIOU])\1', 'c');未选定行
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 中文手册：
(字符串函数和操作符）Database SQL Language Reference：转载地址：http://wyhbx.baihongyu.com/