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

本文涉及的产品
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
简介:

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

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
相关文章
|
1月前
|
关系型数据库 分布式数据库 数据库
PolarDB PostgreSQL版:Oracle兼容的高性能数据库
PolarDB PostgreSQL版是一款高性能的数据库,具有与Oracle兼容的特性。它采用了分布式架构,可以轻松处理大量的数据,同时还支持多种数据类型和函数,具有高可用性和可扩展性。它还提供了丰富的管理工具和性能优化功能,为企业提供了可靠的数据存储和处理解决方案。PolarDB PostgreSQL版在数据库领域具有很高的竞争力,可以满足各种企业的需求。
|
7月前
|
Oracle 关系型数据库 数据库
PostgreSQL和Oracle两种数据库有啥区别?如何选择?
PostgreSQL和Oracle两种数据库有啥区别?如何选择?
184 0
|
8月前
|
SQL Oracle 关系型数据库
物化视图(Oracle与PostgreSQL对比)
物化视图(Oracle与PostgreSQL对比)
|
8月前
|
SQL Oracle 关系型数据库
PostgreSQL技术大讲堂 - 第27讲:Oracle-FDW部署
从零开始学PostgreSQL,PG技术大讲堂 - 第27讲:Oracle-FDW部署
165 2
|
4月前
|
SQL Oracle 关系型数据库
Oracle,Postgresql等数据库使用
Oracle,Postgresql等数据库简单使用
131 0
Oracle,Postgresql等数据库使用
|
7月前
|
SQL Oracle 关系型数据库
Polar DB-O (兼容 Oracle 语法版本)和Polar DB PostgreSQL 版本概述(二)
Polar DB-O (兼容 Oracle 语法版本)和Polar DB PostgreSQL 版本概述(二)
677 0
|
SQL Oracle 关系型数据库
Java连接各种数据库操作(mysql、oracle、postgresql、gbase、mongo)
Java连接各种数据库操作(mysql、oracle、postgresql、gbase、mongo)
437 0
|
11月前
|
SQL 安全 Oracle
MySQL&SQL server&Oracle&Access&PostgreSQL数据库sql注入详解
MySQL&SQL server&Oracle&Access&PostgreSQL数据库sql注入详解
297 0
|
SQL Oracle 网络协议
【.NET 6】使用EF Core 访问Oracle+Mysql+PostgreSQL并进行简单增改操作与性能比较
唠嗑一下。都在说去O或者开源,但是对于数据库选型来说,很多人却存在着误区。例如,去O,狭义上讲,是去Oracle数据库。但是从广义上来说,是去Oracle公司产品或者具有漂亮国垄断地位和需要商业授权的数据库产品。
314 0
【.NET 6】使用EF Core 访问Oracle+Mysql+PostgreSQL并进行简单增改操作与性能比较
|
Oracle 关系型数据库 PostgreSQL

推荐镜像

更多