PostgreSQL去掉表中所有不可见字符的操作

问题描写

数据库中的某些数据中包括了某些不可见字符。ASCII码中的0⑶1,127属于控制字符，不可见。

这些不可见字符常常是不需要的，我们要想办法删除它。

解决办法

写一函数，将所有字段中的不可见字符替换为空格。

测试流程

环境准备

建表，并插入带不可见字符的记录。注：以下数据中的不可见字符在粘贴过来的时候自动去掉了，请按ALT+数字键加入。

CREATE TABLE public.test_table
(

xm character varying(50),
pinyin character varying(200),
sm character(15)
)
WITH (
OIDS=FALSE
);
ALTER TABLE public.test_table
OWNER TO postgres;

— —————————-
— Records of test_table
— —————————-
INSERT INTO ry_syrk_copy VALUES (‘周小星xx’, ‘xx测试’,’测试’ );
INSERT INTO ry_syrk_copy VALUES (‘李华’, ‘测试xx’,’世xx界’ );

注：因不可见字符没法在CSDN中没法打出，故用xx代替。实际上在其它地方可以用ALT+数字键打印出来。

去掉表中所有的不可见字符

— 函数说明：将表test_table中所有的不可见字符替换掉
CREATE OR REPLACE FUNCTION delete_special_char() RETURNS int8 AS $$
DECLARE
row RECORD;
BEGIN

— 查询表中所有类型为字符串的列
FOR row in select column_name from information_schema.columns where table_name = ‘test_table’ and data_type like ‘character%’ LOOP
FOR i IN 1..31 LOOP –替换所有的不可见字符为空格（除chr(0)以外）
EXECUTE ‘update test_table set ‘ || row.column_name || ‘ = replace(‘ || row.column_name || ‘, chr(‘ || i || ‘), ” ” )’;
END LOOP;
END LOOP;

RETURN 1;

END;

$$ LANGUAGE plpgsql;

履行函数

履行函数–select * from delete_special_char()，以后查看表数据，所有的不可见字符都被替换为空格。

补充–怎样删除chr(0)

值得注意的是上述函数其实不能删除chr(0)的不可见字符，见以下测试。

但oracle中上述语句却可以成功履行，下面我就来说一讲吧~

Oracle中varchar2字段的不可见字符处理

在之前的项目中，曾出现加密后的字符串数据丢失，加密内容没法正常解密的情况，经查找缘由，发现是数据库表的varchar2字段中有chr(0)的不可见字符（即我们通常所说的\0），当出现这类情况时，由于java和c++中对字符串处理的区别，将会致使所获得的字符串长度区别。

在java中，字符串的长度可以通过取字符串的字节数组来取得，这样得到的字符串长度为字符串实际的大小（汉字2个字节，其他1个字节）；在c++中通过strlen函数取得的字符串长度为第一个字节\0之前的字符长度。

当我们在编写jni的时候，常常会遇到将java的字符串转换为c++中的字符串的情况，这样，当java中的字符串包括\0的空字节时，在对c++转换后的字符串求取长度时，不要使用strlen函数，否则，其仅仅对\0字节之前的内容求取长度，与实际大小区别。

解决该类问题，根据所属利用的区别，可通过三种手段解决：

在数据库层解决：

Oracle数据库中，可在查询语句中使用函数replace来去除字符串中的非可见字符，例如：

select replace(content,chr(0),null) from bossquery_request where sky_command = ’02’;

以后大家如果遇到类似情况，可通过replace(字段名,chr(ASCII码值),null)来去掉其中对应的ASCII码值的字符。

在java程序中解决：

在java程序中，大家可通过获得String对象的所有字节内容，对字节内容进行扫描，来去掉其中不需要的字节。

在JNI层解决：

在JNI层解决该问题的方式是，不要使用strlen函数来获得字符串长度，可通过GetArrayLength取字节数组长度函数或其他类似函数来获得字符串长度，则可避免该情况产生。

以上为个人经验，希望能给大家一个参考，也希望大家多多支持。如有毛病或未斟酌完全的地方，望不吝赐教。

本文来源：https://www.yuntue.com/post/236125.html | 云服务器网，转载请注明出处！

PostgreSQL去掉表中所有不可见字符的操作

问题描写

解决办法