PostgreSQL去掉表中所有不可见字符的操作
问题描述
数据库中的某些数据中包含了某些不可见字符。ASCII码中的0-31,127属于控制字符，不可见。
这些不可见字符往往是不需要的，我们要想办法删除它。
解决办法
写⼀函数，将所有字段中的不可见字符替换为空格。
测试流程
环境准备
建表，并插⼊带不可见字符的记录。注：下列数据中的不可见字符在粘贴过来的时候⾃动去掉了，请按ALT+数字键加⼊。CREATE st_table
(
xm character varying(50),
pinyin character varying(200),
sm character(15)
)
WITH (
OIDS=FALSE
);
ALTER st_table
OWNER TO postgres;
oracle字符串转数组
-- ----------------------------
-- 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 lumns where table_name = 'test_table' and data_type like 'character%' LOOP
FOR i IN 1..31 LOOP --替换所有的不可见字符为空格（除了chr(0)之外）
EXECUTE 'update test_table set ' || lumn_name || ' = replace(' || lumn_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取字节数组长度函数或者其他类似函数来获取字符串长度，则可避免该情况发⽣。
以上为个⼈经验，希望能给⼤家⼀个参考，也希望⼤家多多⽀持。如有错误或未考虑完全的地⽅，望不吝赐教。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。