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

問(wèn)題描述

數(shù)據(jù)庫(kù)中的某些數(shù)據(jù)中包含了某些不可見字符。ASCII碼中的0-31,127屬于控制字符，不可見。

這些不可見字符往往是不需要的，我們要想辦法刪除它。

解決辦法

寫一函數(shù)，將所有字段中的不可見字符替換為空格。

測(cè)試流程

環(huán)境準(zhǔn)備

建表，并插入帶不可見字符的記錄。注：下列數(shù)據(jù)中的不可見字符在粘貼過(guò)來(lái)的時(shí)候自動(dòng)去掉了，請(qǐng)按ALT+數(shù)字鍵加入。

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測(cè)試','測(cè)試' );
INSERT INTO ry_syrk_copy VALUES ('李華', '測(cè)試xx','世xx界' );

注：因不可見字符無(wú)法在CSDN中無(wú)法打出，故用xx代替。實(shí)際上在其它地方可以用ALT+數(shù)字鍵打印出來(lái)。

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

-- 函數(shù)說(shuō)明：將表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;

執(zhí)行函數(shù)

執(zhí)行函數(shù)–select * from delete_special_char()，之后查看表數(shù)據(jù)，所有的不可見字符都被替換為空格。

補(bǔ)充–如何刪除chr(0)

值得注意的是上述函數(shù)并不能刪除chr(0)的不可見字符，見如下測(cè)試。

但oracle中上述語(yǔ)句卻可以成功執(zhí)行，下面我就來(lái)講一講吧~

Oracle中varchar2字段的不可見字符處理

在以前的項(xiàng)目中，曾經(jīng)出現(xiàn)加密后的字符串?dāng)?shù)據(jù)丟失，加密內(nèi)容無(wú)法正常解密的情況，經(jīng)查找原因，發(fā)現(xiàn)是數(shù)據(jù)庫(kù)表的varchar2字段中有chr(0)的不可見字符（即我們通常所說(shuō)的\0），當(dāng)出現(xiàn)這種情況時(shí)，由于java和c++中對(duì)字符串處理的不同，將會(huì)導(dǎo)致所取得的字符串長(zhǎng)度不同。

在java中，字符串的長(zhǎng)度可以通過(guò)取字符串的字節(jié)數(shù)組來(lái)獲得，這樣得到的字符串長(zhǎng)度為字符串實(shí)際的大?。h字2個(gè)字節(jié)，其他1個(gè)字節(jié)）；在c++中通過(guò)strlen函數(shù)獲得的字符串長(zhǎng)度為第一個(gè)字節(jié)\0之前的字符長(zhǎng)度。

當(dāng)我們?cè)诰帉慾ni的時(shí)候，經(jīng)常會(huì)遇到將java的字符串轉(zhuǎn)換為c++中的字符串的情況，這樣，當(dāng)java中的字符串包含\0的空字節(jié)時(shí)，在對(duì)c++轉(zhuǎn)換后的字符串求取長(zhǎng)度時(shí)，不要使用strlen函數(shù)，否則，其僅僅對(duì)\0字節(jié)之前的內(nèi)容求取長(zhǎng)度，與實(shí)際大小不同。

解決該類問(wèn)題，根據(jù)所屬應(yīng)用的不同，可通過(guò)三種手段解決：

在數(shù)據(jù)庫(kù)層解決：

Oracle數(shù)據(jù)庫(kù)中，可在查詢語(yǔ)句中使用函數(shù)replace來(lái)去除字符串中的非可見字符，例如：

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

以后大家如果遇到類似情況，可通過(guò)replace(字段名,chr(ASCII碼值),null)來(lái)去掉其中對(duì)應(yīng)的ASCII碼值的字符。

在java程序中解決：

在java程序中，大家可通過(guò)獲取String對(duì)象的所有字節(jié)內(nèi)容，對(duì)字節(jié)內(nèi)容進(jìn)行掃描，來(lái)去掉其中不需要的字節(jié)。

在JNI層解決：

在JNI層解決該問(wèn)題的方式是，不要使用strlen函數(shù)來(lái)獲取字符串長(zhǎng)度，可通過(guò)GetArrayLength取字節(jié)數(shù)組長(zhǎng)度函數(shù)或者其他類似函數(shù)來(lái)獲取字符串長(zhǎng)度，則可避免該情況發(fā)生。

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教。