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

问题描述

数据库中的某些数据中包含了某些不可见字符。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取字节数组长度函数或者其他类似函数来获取字符串长度，则可避免该情况发生。

以上为个人经验，希望能给大家一个参考，也希望大家多多支持我们。如有错误或未考虑完全的地方，望不吝赐教。

赞 (0)

相关推荐

  • 浅谈PostgreSQL 11 新特性之默认分区

    文章目录 PosgtreSQL 11 支持为分区表创建一个默认(DEFAULT)的分区,用于存储无法匹配其他任何分区的数据.显然,只有 RANGE 分区表和 LIST 分区表需要默认分区. CREATE TABLE measurement ( city_id int not null, logdate date not null, peaktemp int, unitsales int ) PARTITION BY RANGE (logdate); CREATE TABLE measuremen

  • postgresql 导出建表语句的命令操作

    postgresql 导出建表语句的命令操作

    我就废话不多说了,大家还是直接看命令吧~ pg_dump -h host -U postgres -d dbname -O -s -t "table_name" > name.sql 补充:PostgreSQL使用pg_dump导出单个数据表的insert语句 PostgreSQL自带的pgadmin4工具没有办法生成数据表的insert语句,这使得想要提取单个表的数据,插入到不同环境的相同表中变成很麻烦的一件事,使用PostgreSQL的pg_dump工具可以实现该功能. 使用

  • 使用python将mdb数据库文件导入postgresql数据库示例

    mdb格式文件可以通过mdbtools工具将内中包含的每张表导出到csv格式文件.由于access数据库和postgresQL数据库格式上会存在不通性,所以使用python的文件处理,将所得csv文件修改成正确.能识别的格式. 导入脚本说明(此脚本运行于linux): 1.apt-get install mdbtools,安装mdbtools工具 2.将mdb 文件拷贝到linux虚拟机中,修改脚本中mdb文件目录'dir' 3.修改服务器及数据库配置 4.执行脚本 复制代码 代码如下: # -

  • docker安装并持久化postgresql数据库的操作步骤

    docker安装并持久化postgresql数据库的操作步骤

    安装docker步骤略过 1.拉取postgresql镜像 docker pull postgresql 2.创建本地卷,数据卷可以在容器之间共享和重用,默认会一直存在,即使容器被删除(docker volume inspect pgdata可查看数据卷的本地位置) docker volume create pgdata 3.启动容器 docker run --name postgres2 -e POSTGRES_PASSWORD=password -p 5432:5432 -v pgdata:

  • PostgreSQL 重复数据处理的操作方法

    PostgreSQL 重复数据处理的操作方法

    PostgreSQL简介 PostgreSQL是一种特性非常齐全的自由软件的对象-关系型数据库管理系统(ORDBMS),是以加州大学计算机系开发的POSTGRES,4.2版本为基础的对象关系型数据库管理系统.POSTGRES的许多领先概念只是在比较迟的时候才出现在商业网站数据库中.PostgreSQL支持大部分的SQL标准并且提供了很多其他现代特性,如复杂查询.外键.触发器.视图.事务完整性.多版本并发控制等.同样,PostgreSQL也可以用许多方法扩展,例如通过增加新的数据类型.函数.操作符

  • postgresql 实现数据的导入导出

    postgresql 实现数据的导入导出

    最近想把服务器上的测试数据库数据导到我本地的电脑上,本地电脑数据库是安装在windows系统下 之前没使用过pgsql,网上找了点资料,记入如下: 一,首先把服务器上的数据进行备份 pg_dump -U 用户名 数据库名 (-t 表名)> 数据存放路径 二,把.sql 文件下载到本地之后,首先切换到pgsql路径下的bin目录 然后执行这条命令: -d:数据库名 -h:地址 -p:端口 -u:用户名 -f:sql文件路径 之后输入口令: 这样就可以了! 补充:Sqoop从PostgreSQL导入

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

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

    问题描述 数据库中的某些数据中包含了某些不可见字符.ASCII码中的0-31,127属于控制字符,不可见. 这些不可见字符往往是不需要的,我们要想办法删除它. 解决办法 写一函数,将所有字段中的不可见字符替换为空格. 测试流程 环境准备 建表,并插入带不可见字符的记录.注:下列数据中的不可见字符在粘贴过来的时候自动去掉了,请按ALT+数字键加入. CREATE TABLE public.test_table ( xm character varying(50), pinyin character

  • postgresql 查询字符串中是否包含某字符的操作

    postgresql 查询字符串中是否包含某字符的操作

    先看表结构和数据: 需求: 查询出manager_id中包含某些字符的数据,此处查询manager_id包含6651545956882725395,5722501350582149881的数据, SQL语句如下: SELECT t.* from bas_cm_customer t where position('6651545956882725395' in t.manager_id) > 0 OR position ('5722501350582149881' in t.manager_id)

  • 用Java正则去掉字符串中重复出现的字符

    String str = "abcdeabcdeabcdeaaaaaadddddceeeeabcccccccacadaeec"; str = str.replaceAll(reg, ""); System.out.println(str); str = str.replaceAll("(?s)(.)(?=.*\\1)", ""); (?s)(.)(?=.*\1) (?s) 开启单行模式 DOTALL 让. 号匹配任意字符 (.

  • 解决postgresql表中的字段名称包含特殊符号的问题

    在创建postgresql表的时候,由于字段中包含了"()"这种特殊字符,导致创建表的时候报错. 解决方法: 给包含特殊字符的字段加上双引号"",再进行创建表即可 例如: create table freighttransport_tab ( id serial, 日期 TEXT, "重量(公斤)" TEXT ) 此时"重量(公斤)"字段包含特殊符号(),创建表时,在sql语句中对该字段添加双引号即可. 补充:PostgreS

  • PostgreSQL向数据库表中添加行数据的操作

    PostgreSQL向数据库表中添加行数据的操作

    实例: 例如数据库中有一个2013Rainfall数据表 想在下面接着插入一行数据,在pgAmin III 界面中,点击SQL,并其界面下输入, 如下图所示 并运行,同时刷新编辑数据界面,可看到数据插入成功, 如下图所示 补充:postgresql向表中插入大量数据 不使用存储过程: insert into schema.table select generate_series(1,10000),'a'; 以上表中有一列int类型列和一列char型列,generate_series(1,1000

  • springboot jpa之返回表中部分字段的处理详解

    springboot jpa之返回表中部分字段的处理详解

    目录 springboot jpa返回表中部分字段 JPA 自定义返回字段 springboot jpa返回表中部分字段 使用springboot jpa操作数据库可以加快我们的开发效率,对于简单的crud操作来说,使用jpa来开发不要太爽,但是说实话对于一些复杂的数据库操做jpa使用起来就不是这么爽了. 在开发中很多时候我们要返回的可能只是数据库表中或某个类中的一部分字段,这个要是用mybatis的话就很简单,直接在sql中select字段就好了,规范一点就数据传输类接一下,偷个懒的话直接用m

  • postgreSQL数据库 实现向表中快速插入1000000条数据

    postgreSQL数据库 实现向表中快速插入1000000条数据

    不用创建函数,直接向表中快速插入1000000条数据 create table tbl_test (id int, info text, c_time timestamp); insert into tbl_test select generate_series(1,100000),md5(random()::text),clock_timestamp(); select count(id) from tbl_test; --查看个数据条数 补充:postgreSQL 批量插入10000条数据

  • 在PostgreSQL中设置表中某列值自增或循环方式

    在postgresql中,设置已存在的某列(num)值自增,可以用以下方法: //将表tb按name排序,利用row_number() over()查询序号并将该列命名为rownum,创建新表tb1并将结果保存到该表中 create table tb1 as (select *, row_number() over(order by name) as rownum from tb); //根据两张表共同的字段name,将tb1中rownum对应值更新到tb中num中 update tb set

  • Oracle截取字符串去掉字段末尾指定长度的字符

    lengthb(string)计算string所占的字节长度:返回字符串的长度,单位是字节 length(string)计算string所占的字符长度:返回字符串的长度,单位是字符 eg: //去掉该字段后面15位字符串 select t.depre_name, substr(t.depre_name, 0, (length(t.depre_name) - 16)) from table t where t.del_flag = '0' [备注]一个汉字在Oracle数据库里占多少字节跟数据库的

  • postgresql 导入数据库表并重设自增属性的操作

    postgresql 导入数据库表并重设自增属性的操作

    postgresql使用Navicat软件导出数据库表,在导入会数据库的操作. postgresql 的自增字段 是通过 序列 sequence来实现的. 1.先删除导出的数据库表中的自增属性 2.导入数据库表之后,需要创建序列. 注:一般序列名称由数据表名+主键字段+seq组成 (通常情况下主键字段即为自增字段),如下表名为cof_table_hot_analysis,主键字段为 id CREATE SEQUENCE cof_table_hot_analysis_id_seq START WI

随机推荐