开发者社区> 瀚高大李> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

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

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

相关文章
10 PostgreSQL 表级复制-物化视图篇, 支持异地,异构如 Oracle 到 pg 的物化视图|学习笔记
快速学习10 PostgreSQL 表级复制-物化视图篇,支持异地,异构如 Oracle 到 pg 的物化视图
0 0
关于PostgreSQL数据库兼容Oracle数据库闪回查询的实现方案
注:关于在PostgreSQL上面实现Oracle数据库的闪回功能(闪回查询 闪回表 闪回删除…)的这个想法已经有很长时间了,但是鉴于本人的能力 精力和身体条件 迟迟没有完成。期间也有很多的小伙伴跟我一起研究过这个功能,但是最终都因为各种各样的问题 没有做下去。Oracle数据库闪回功能跨越版本较大,功能也比较强大 在PostgreSQL数据库上实现,需要对数据库内核有很深入的理解 两大数据库不同的底层原理也终将影响各自的实现策略,PostgreSQL标记删除就地插入的特点和基于事务快照行可见性的特性是我们可以开发PostgreSQL闪回查询的大前提。本文主要介绍 实现闪回查询的 一种实现方案
0 0
【学习资料】第10期数据库选型思考(PostgreSQL,MySQL,Oracle)
大家好 ,这里是数据库选型思考(PostgreSQL,MySQL,Oracle)
0 0
【学习资料】第1期Oracle DBA 增值 PostgreSQL,Greenplum 学习计划 - 珍藏级
大家好,这里是Oracle DBA 增值 PostgreSQL,Greenplum 学习计划 - 珍藏级
0 0
【学习视频】第6期2019-Oracle迁移到PostgreSQL - 适合DBA与业务开发者
大家好,这里是《PG干O,仁心所象 - 去O实战培训》 公益活动纪录 - 《PG开发者指南、去O、管理与优化实践》
0 0
PostgreSQL Oracle 兼容性 - Oracle 19c 新特性在PostgreSQL中的使用
PostgreSQL Oracle 兼容性 - Oracle 19c 新特性在PostgreSQL中的使用
0 0
PostgreSQL Oracle 兼容性之 - performance insight - AWS performance insight 理念与实现解读 - 珍藏级
PostgreSQL , perf insight , 等待事件 , 采样 , 发现问题 , Oracle 兼容性
0 0
Oracle/Mysql迁移到Postgresql事务回滚行为差异(开发避坑系列)
Mysql或Oracle迁移到Postgresql系产品后,经常会发生事务回滚导致的问题,具体问题一般都是类似于: **为什么我没rollback,我的事务就自己回滚了?** 下面我举一个简单的例子,说明下PG和其他两款DB在事务回滚行为上的差异 ## Oracle事务内报错后的行为 (完整代码贴在文章最后) ```java Class.fo
0 0
+关注
文章
问答
文章排行榜
最热
最新
相关电子书
更多
EasyDBforOracle— 基于阿里云的Oracle最佳实践
立即下载
Oracle 至PostgreSQL案例分享
立即下载
PostgresChina2018_王帅_从Oracle到PostgreSQL的数据迁移
立即下载