Oracle通过sql语句过滤ASCII控制字符

首先我们聊一聊ASCII控制字符，这些控制字符是不可见的，可以理解为一种特殊字符，而这种字符是可以被存入Oracle表中的。

项目中遇到了这样一种情况，java程序从Oracle表中取数据并解析为xml时产生了报错信息，原因是表内存在无法被解析为xml的特殊字符，后来导出数据后使用notepad++工具查看时才发现存在ASCII控制字符。注意，这些ASCII控制字符是无法被xml解析的，所以需要想办法将其过滤。

 

ASCII控制字符

二进制	十进制	十六进制	缩写	可以显示的表示法	名称/意义
0000 0000	0	00	NUL	␀	空字符（Null）
0000 0001	1	01	SOH	␁	标题开始
0000 0010	2	02	STX	␂	本文开始
0000 0011	3	03	ETX	␃	本文结束
0000 0100	4	04	EOT	␄	传输结束
0000 0101	5	05	ENQ	␅	请求
0000 0110	6	06	ACK	␆	确认回应
0000 0111	7	07	BEL	␇	响铃
0000 1000	8	08	BS	␈	退格
0000 1001	9	09	HT	␉	水平定位符号
0000 1010	10	0A	LF	␊	换行键
0000 1011	11	0B	VT	␋	垂直定位符号
0000 1100	12	0C	FF	␌	换页键
0000 1101	13	0D	CR	␍	归位键
0000 1110	14	0E	SO	␎	取消变换（Shift out）
0000 1111	15	0F	SI	␏	启用变换（Shift in）
0001 0000	16	10	DLE	␐	跳出数据通讯
0001 0001	17	11	DC1	␑	设备控制一（XON 启用软件速度控制）
0001 0010	18	12	DC2	␒	设备控制二
0001 0011	19	13	DC3	␓	设备控制三（XOFF 停用软件速度控制）
0001 0100	20	14	DC4	␔	设备控制四
0001 0101	21	15	NAK	␕	确认失败回应
0001 0110	22	16	SYN	␖	同步用暂停
0001 0111	23	17	ETB	␗	区块传输结束
0001 1000	24	18	CAN	␘	取消
0001 1001	25	19	EM	␙	连接介质中断
0001 1010	26	1A	SUB	␚	替换
0001 1011	27	1B	ESC	␛	跳出
0001 1100	28	1C	FS	␜	文件分割符
0001 1101	29	1D	GS	␝	组群分隔符
0001 1110	30	1E	RS	␞	记录分隔符
0001 1111	31	1F	US	␟	单元分隔符
0111 1111	127	7F	DEL	␡	删除

 

那么，如何过滤掉这些ASCII控制字符呢。

 

方法1：通过java程序直接过滤掉ASCII控制字符。

此方法仅可用于方便变更java代码的情况，本文不作描述。而当无法动java代码时，我们就需要从sql的角度去过滤掉这些ASCII控制字符了，可以参考如下方法。

 

方法2：通过sql去过滤ASCII控制字符。

我们可以通过语句来确认ASCII的序号。

select ascii('') from dual;  --此处无法正常显示，可参考截图
select chr(2) from dual;

可以得知该ASCII控制字符对应的十进制数为2。我们将这个特殊字符拷贝至notepad++，发现对应以上表格内的可显示字符为'stx'，与语句查出来的10进制数一致。

所以我们可以通过translate函数来批量过滤特殊字符：

translate('目标字段',
chr(0)||chr(1)||chr(2)||chr(3)||chr(4)||chr(5)||chr(6)||chr(7)||chr(8)||chr(9)||
chr(10)||chr(11)||chr(12)||chr(13)||chr(14)||chr(15)||chr(16)||chr(17)||chr(18)||chr(19)||
chr(20)||chr(21)||chr(22)||chr(23)||chr(24)||chr(25)||chr(26)||chr(27)||chr(28)||chr(29)||chr(30)||chr(31)
,' ')

注意以上最后一行单引号内有个'空格'，如果不加空格将无法实现替换操作。

这样我们就可以批量的过滤掉ASCII控制字符了。

我们可以通过查询系统dual表的方式验证一下语句的准确性：

 

这样我们就可以很轻松的从数据库内取值时过滤掉无法解析为xml字符串的ASCII控制字符了。