20171204_03.md

PostgreSQL Oracle兼容性 - substrb - orafce substrb 适配 Oracle substrb

作者

digoal

日期

2017-12-04

标签

PostgreSQL , substrb , 按字节截断 , 不截断多字节字符

---

背景

在orafce提供的substrb中，当截断到某个多字节字符时，如果字符被中间截断，会导致乱码。

为了兼容oracle substrb，不截断多字节字符，可以在orafce包的substrb基础之上再写一个plpgsql函数来代替。

兼容oracle substrb

下面是阿里云提供的方法。

create extension orafce;  
  
CREATE OR REPLACE FUNCTION substrb_new(v_text text, start integer, count integer)  
  RETURNS character varying AS  
$BODY$  
  
DECLARE  
ret text default '';  
  
len integer default 0;  
len1 integer default 0;  
  
start1 integer default 0;  
end1 integer;  
  
cc varchar;  
  
BEGIN  
    -- execute  'select substrb(''123中国123'',3,2)'  into v_text;  
	len = NVL(length(v_text), 0);  
	end1 = start + count;  
  
	for pos in 0..len-1 loop  
		if len1 >= start - 1 THEN  
			exit;  
		end if;  
		len1 = len1 + octet_length(SUBSTR (v_text, pos+1, 1));  
		start1 = start1 + 1;  
	end loop;  
  
	for pos in start1..len-1 loop  
		cc = SUBSTR (v_text, pos+1, 1);  
		len1 = len1 + octet_length(cc);  
  
		if len1 > end1 - 1 THEN  
			exit;  
		end if;  
		ret = ret || cc;  
	end loop;  
    return ret;  
END;  
$BODY$  
  LANGUAGE 'plpgsql' VOLATILE;  

UTF8字符集，中不会被截断。

postgres=# select octet_length(substrb_new('123中国123',3,2));  
 octet_length   
--------------  
            1  
(1 row)  
  
postgres=# select octet_length(substrb_new('123中国123',3,3));  
 octet_length   
--------------  
            1  
(1 row)  
  
postgres=# select octet_length(substrb_new('123中国123',3,4));  
 octet_length   
--------------  
            4  
(1 row)  
  
postgres=# select octet_length(substrb_new('123中国123',3,1));  
 octet_length   
--------------  
            1  
(1 row)  
  
postgres=# select substrb_new('123中国123',3,1);  
 substrb_new   
-------------  
 3  
(1 row)  
  
postgres=# select substrb_new('123中国123',3,2);  
 substrb_new   
-------------  
 3  
(1 row)  
  
postgres=# select substrb_new('123中国123',3,3);  
 substrb_new   
-------------  
 3  
(1 row)  
  
postgres=# select substrb_new('123中国123',3,4);  
 substrb_new   
-------------  
 3中  
(1 row)  

orafce提供的substrb截断多字节字符时，可能产生乱码或不可见字符。

postgres=# select substrb('123中国123',3,1)::bytea;  
 substrb   
---------  
 \x33  
(1 row)  
  
postgres=# select substrb('123中国123',3,2)::bytea;  
 substrb   
---------  
 \x33e4  
(1 row)  
  
postgres=# select substrb('123中国123',3,3)::bytea;  
 substrb    
----------  
 \x33e4b8  
(1 row)  
  
postgres=# select substrb('123中国123',3,4)::bytea;  
  substrb     
------------  
 \x33e4b8ad  
(1 row)  

PostgreSQL 许愿链接

您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.

9.9元购买3个月阿里云RDS PostgreSQL实例

PostgreSQL 解决方案集合

德哥 / digoal's github - 公益是一辈子的事.

PolarDB 学习图谱: 训练营、培训认证、在线互动实验、解决方案、生态合作、写心得拿奖品

购买PolarDB云服务折扣活动进行中, 55元起

About 德哥