Oracle数据库中如何去掉NUL字符？

数据库： 我有一个 Oracle11g 数据库，其中有一个包含大约 1000 万行和大约 40 列的表。这些数据源自打孔卡时代，并已从一个 Oracle 版本转换到下一个版本多次。这是一个实时生产数据库，并且在某种程度上持续使用。但它并不是那么重要（尤其是在夜间），以至于我无法通过昂贵的查询和更新来对其施加压力。所以没关系。

编辑：字符集是 AL32UTF8。

问题： 我注意到有些列包含 NUL 字符。我发现由 1-4 个 NUL 字符组成的值，但理论上它们的数量可能多于该列可用的字符数。我想去掉 NUL 字符。如果值中只有 NUL 字符，我想将该列的值更改为 SQL NULL。如果其他字符之间有 NUL 字符（我还没有看到任何情况），我想删除它们（用“”替换）。

我尝试过的:

我注意到select rawtohex(mycolumn) from mytable例如返回“000000”（3 个 NUL 字符）。

select rawtohex('A') from dual;返回“61”

select rawtohex('Ä') from dual;返回“C385”。

我已经探索过寻找这样的 NUL 字符：

SELECT DISTINCT mycolumn 
FROM mytable
WHERE rawtohex(mycolumn) LIKE '%00%;'

到目前为止，我还没有找到任何包含 rawtohex 包含“00”字符的列，只有 NUL 字符的列除外。所以看起来像使用LIKE '%00%'是安全的。但我不确定 oracle 的 rawtohex 实现以及它使用哪种十六进制编码。

所以......当我继续探索这条道路并最终编写一个脚本来修复垃圾时，我问是否有人以前遇到过这项工作以及你是如何解决它的。 :)

我个人会用CHR() http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions026.htm#SQLRF00616来识别空值。 nul 是 ASCII 0 并且CHR()将返回您传入的数字的字符表示形式。

SQL> with the_data as (
  2  select 'a' || chr(0) || 'b' as str from dual
  3   union all
  4  select 'a' || 'c' from dual
  5         )
  6  select dump(str)
  7    from the_data
  8   where str like '%' || chr(0) || '%'
  9         ;

DUMP(STR)
----------------------------------------------------    
Typ=1 Len=3: 97,0,98

正如您通过连接百分号所看到的CHR(0)（相当于 nul）您可以返回带有 nul 的行。

DUMP() http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions055.htm#SQLRF00635返回数据类型（1 表示 VARCHAR2 http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements001.htm#BABCGCHG) 字符串的长度（以字节为单位）和数据的内部表示；默认为二进制。

然而，你需要小心多字节数据 as CHR()返回数字 256 模数的等效字符：

SQL> with the_data as (
  2  select 'a' || chr(0) || 'b' as str from dual
  3   union all
  4  select 'a' || chr(256) || 'c' from dual
  5         )
  6  select dump(str)
  7    from the_data
  8   where str like '%' || chr(0) || '%'
  9         ;

DUMP(STR)
-------------------------------------------------
Typ=1 Len=3: 97,0,98
Typ=1 Len=4: 97,1,0,99

正如你所看到的，你会在这里错误地识别出一个 nul，使用CHR() or DUMP()

换句话说，如果您没有多字节数据，那么最简单的事情就是replace http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions153.htm#SQLRF00697 it:

update <table>
   set <column> = replace(<column>, chr(0));

利用RAWTOHEX()有类似的问题；虽然你可以找到00不能保证它实际上是一个 nul：

SQL> with the_data as (
  2  select 'a' || chr(0) || 'b' as str from dual
  3   union all
  4  select 'a' || chr(256) || 'c' from dual
  5         )
  6  select rawtohex(str)
  7    from the_data
  8   where str like '%' || chr(0) || '%'
  9         ;

RAWTOHEX
--------
610062
61010063

它实际上还有一个更进一步的问题；想象你有两个角色10 and 06那么返回值是1006你会发现00。如果要使用此方法，您必须确保只查看从字符串开头开始的两个字符组。

由于 nul 字符的内部表示用于表示其他多字节字符的一部分，因此您不能直接替换它们，因为您不知道它是一个字符还是半个字符。因此，如果您使用多字节字符集，据我所知，您将无法执行此操作。