开发者社区> 德哥> 正文

PostgreSQL Oracle 兼容性之 - ASCIISTR

简介:
+关注继续查看

背景

在Oracle中有一个函数asciistr,可以将非ASCII字符转换成UTF-16编码的字符,因此转换后的字符串可以存储在只支持ASCII编码的数据库中。

ASCIISTR takes as its argument a string, or an expression that resolves to a string, in any character set and returns an ASCII version of the string in the database character set.

Non-ASCII characters are converted to the form \xxxx, where xxxx represents a UTF-16 code unit.

例子

SELECT ASCIISTR('ABÄCDE') FROM DUAL;

ASCIISTR('
----------
AB\00C4CDE

\00C4即转换后的UTF-16编码,这样整个字符串就可以存储在ASCII编码的数据库中了。

PostgreSQL ASCIISTR

了解了ASCIISTR的目的之后,我们就可以在PostgreSQL中实现对非ASCII编码的字符转换了。

PostgreSQL数据库没有UTF-16字符集,但是它支持UTF-8,UTF-8是变长字符集,支持全球所有的语言字符编码。

PostgreSQL提供了几个转换函数如下

postgres=# \df convert*
                              List of functions
   Schema   |     Name     | Result data type | Argument data types |  Type  
------------+--------------+------------------+---------------------+--------
 pg_catalog | convert      | bytea            | bytea, name, name   | normal
 pg_catalog | convert_from | text             | bytea, name         | normal
 pg_catalog | convert_to   | bytea            | text, name          | normal
(3 rows)

例子,支持来回转换

postgres=# select convert_to('abc你好中国cde','UTF8');
               convert_to               
----------------------------------------
 \x616263e4bda0e5a5bde4b8ade59bbd636465
(1 row)

postgres=# select convert_from(convert_to('abc你好中国cde','UTF8'),'UTF8');
  convert_from  
----------------
 abc你好中国cde
(1 row)

postgres=# select convert_from('\x616263e4bda0e5a5bde4b8ade59bbd636465', 'UTF8');
  convert_from  
----------------
 abc你好中国cde
(1 row)

方法2,使用textsend函数,这个函数没有编码输入,所以需要设置client_encoding。

postgres=# set client_encoding='UTF8';
SET
postgres=# select textsend('abc你好中国cde');
                textsend                
----------------------------------------
 \x616263e4bda0e5a5bde4b8ade59bbd636465
(1 row)

postgres=# set client_encoding='GBK';
SET
postgres=# select textsend('abc你好中国cde');
ERROR:  character with byte sequence 0xad 0xe5 in encoding "GBK" has no equivalent in encoding "UTF8"
LOCATION:  report_untranslatable_char, wchar.c:2051
postgres=# select textsend('abc你好cde');
          textsend          
----------------------------
 \x616263e4bda0e5a5bd636465
(1 row)

PostgreSQL支持的字符编码转换函数在这里

postgres=# select * from pg_conversion ;
            conname             | connamespace | conowner | conforencoding | contoencoding |            conproc             | condefault 
--------------------------------+--------------+----------+----------------+---------------+--------------------------------+------------
 ascii_to_mic                   |           11 |       10 |              0 |             7 | ascii_to_mic                   | t
 mic_to_ascii                   |           11 |       10 |              7 |             0 | mic_to_ascii                   | t
......
 utf8_to_shift_jis_2004         |           11 |       10 |              6 |            41 | utf8_to_shift_jis_2004         | t
 euc_jis_2004_to_shift_jis_2004 |           11 |       10 |              5 |            41 | euc_jis_2004_to_shift_jis_2004 | t
 shift_jis_2004_to_euc_jis_2004 |           11 |       10 |             41 |             5 | shift_jis_2004_to_euc_jis_2004 | t
(132 rows)

以上例子把整个字符串都进行了转换,包括一些已经是ASCII的字符也被转换了,转换后长度变长了,如果你要尽量缩小长度怎么办呢?

如果你要对一个字符串中非ASCII字符转换为bytea,而ASCII字符保持不变,则需要自定义PostgreSQL函数,如下demo

create or replace function asciistr(str text, encoding text) returns text[] as $$
declare
  mid text;
  res text[] := (array[])::text[];
begin
  foreach mid in array regexp_split_to_array(str, '')
  loop
    if ascii(mid)<256 then
      res := array_append(res, mid);
    else
      res := array_append(res, (convert_to(mid,encoding))::text);
    end if;
  end loop;
  return res;
end;
$$
language plpgsql strict;

测试

postgres=# select asciistr('abc中国你好ced', 'UTF8');
                           asciistr                            
---------------------------------------------------------------
 {a,b,c,"\\xe4b8ad","\\xe59bbd","\\xe4bda0","\\xe5a5bd",c,e,d}
(1 row)

你还可以输出字符串,也可以直接输出字符串

postgres=# select array_to_string(asciistr('abc中国你好ced', 'UTF8'), '');
            array_to_string             
----------------------------------------
 abc\xe4b8ad\xe59bbd\xe4bda0\xe5a5bdced
(1 row)

使用这种方法,在反转换时,也要通过函数来处理。

create or replace function reverse_asciistr(str text[], encoding text) returns text as $$
declare
  mid text;
  res text := '';
begin
  foreach mid in array str
  loop
    if mid ~ '^\\x' then
      res := concat(res, convert_from(mid::bytea, encoding));
    else
      res := concat(res, mid);
    end if;
  end loop;
  return res;
end;
$$
language plpgsql strict;

反转转换举例

postgres=# select reverse_asciistr(asciistr('abc_12\ab你好\ade中国_1jjr3', 'UTF8'), 'UTF8');
      reverse_asciistr       
-----------------------------
 abc_12\ab你好\ade中国_1jjr3
(1 row)

参考

https://www.postgresql.org/docs/9.6/static/multibyte.html

Count

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

相关文章
阿里云服务器如何登录?阿里云服务器的三种登录方法
购买阿里云ECS云服务器后如何登录?场景不同,阿里云优惠总结大概有三种登录方式: 登录到ECS云服务器控制台 在ECS云服务器控制台用户可以更改密码、更换系.
30121 0
阿里云服务器如何登录?阿里云服务器的三种登录方法
购买阿里云ECS云服务器后如何登录?场景不同,大概有三种登录方式:
14037 0
ArcGIS 客户端跨版本连接Oracle 地理数据库时的兼容性说明
很多用户都会有这样的疑问: 1:我是否可以使用ArcGIS Desktop9.3连接ArcSDE10 2:我是否可以使用ArcGIS 10.
1016 0
PostgreSQL Oracle 兼容性之 - PL/SQL FORALL, BULK COLLECT
Oracle PL/SQL 开发的童鞋,一定对O家的bulk批量处理的性能很是赞赏吧。但是PostgreSQL用户请不要垂涎,作为学院派和工业界的一颗璀璨明珠。开源数据库PostgreSQL,也有对应的批量处理策略哦。下面是一组LOOP和BULK的性能测试数据 一起来耍耍吧,先看看Oracle怎么
3937 0
+关注
德哥
公益是一辈子的事, I&#39;m digoal, just do it.
2153
文章
245
问答
来源圈子
更多
阿里云数据库:帮用户承担一切数据库风险,给您何止是安心!支持关系型数据库:MySQL、SQL Server、PostgreSQL、PPAS(完美兼容Oracle)、自研PB级数据存储的分布式数据库Petadata、自研金融级云数据库OceanBase支持NoSQL数据库:MongoDB、Redis、Memcache更有褚霸、丁奇、德哥、彭立勋、玄惭、叶翔等顶尖数据库专家服务。
+ 订阅
相关文档: 云数据库 OceanBase 版 可信账本数据库 云原生关系型数据库 PolarDB PostgreSQL引擎
文章排行榜
最热
最新
相关电子书
更多
JS零基础入门教程(上册)
立即下载
性能优化方法论
立即下载
手把手学习日志服务SLS,云启实验室实战指南
立即下载