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

Posted on by

問題描述

數據庫中的某些數據中包含瞭某些不可見字符。ASCII碼中的0-31,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取字節數組長度函數或者其他類似函數來獲取字符串長度，則可避免該情況發生。

以上為個人經驗，希望能給大傢一個參考，也希望大傢多多支持WalkonNet。如有錯誤或未考慮完全的地方，望不吝賜教。

推薦閱讀：