oracle - 如何摆脱Oracle数据库中的NUL字符？

Question

数据库 :
我有一个 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%'是安全的。但我不确定 rawtohex 的 oracle 实现以及它使用什么样的十六进制编码。

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

Answer 1

我个人会使用 CHR() 识别空值。 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() 返回数据类型( 1 means VARCHAR2 )字符串的长度(以字节为单位)和数据的内部表示；默认是二进制的。

然而， 您需要小心处理多字节数据 如 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

如您所见，使用 CHR() 时，您会在这里错误地识别空值。或 DUMP()
换句话说，如果您没有多字节数据，那么最简单的方法就是 replace它:

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

使用 RAWTOHEX()有类似问题；虽然你可以找到 00不能保证它实际上是一个空值:

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和 06然后返回的值是 1006你会发现 00 .如果要使用此方法，则必须确保从字符串的开头只查看两个字符组。

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