从Oracle Varchar2中查找和删除非ASCII字符

Question

我们目前正在将其中一个Oracle数据库迁移到UTF8，并发现有一些记录接近于4000字节的varchar限制。当我们尝试迁移这些记录时，会失败，因为它们包含成为多字节UF8字符的字符。 我想在PL/SQL中完成的是定位这些字符以查看它们是什么，然后更改或删除它们。

我想做的是：

SELECT REGEXP_REPLACE(COLUMN,'[^[:ascii:]],'')

但 Oracle 没有实现 [:ascii:] 字符类。

有没有简单的方法可以做到我想要做的事情？

- Paul Gilfedder

---

您可能希望将 ç 替换为 c 等。丢弃整个字符比丢弃变音符号更糟糕。 - MSalters

我们首先需要弄清楚这些字符是什么，然后再决定如何处理它们。 - Paul Gilfedder

这是一个破坏性的过程，您是否想要保留一些字符的ASCII替换？例如破折号、单引号、双引号等等。unistr 0013-, 0018'，0019'，001C"，001D"。 - Sun

Answer 1

我认为这样就可以了：

SELECT REGEXP_REPLACE(COLUMN, '[^[:print:]]', '')

- Iurii Tkachenko

---

这很简洁而且功能良好。作为补充，你也可以使用REGEXP_REPLACE(Column,'[^ -~]','')，而不是上面提到的所有Chr()函数和字符串连接。 - Ciarán

@Ciaran说：REGEXP_REPLACE(Column,'[^ -~]','')非常棒，因为Oracle不支持'[\x80-\xFF]'。应该在答案中加入这个内容。 - mivk

如果您想保留换行符，请使用regexp_replace(column, '[^ -~|[:space:]]', '')。 - Learner

它指定了ASCII字符范围，即空格（字符32）到波浪号"~"（字符126）http://www.asciitable.com - Ciarán

这是一个不错的开始，但“print”类中还有很多字符没有被找到或删除。这绝对让我朝着正确的方向前进，所以感谢您添加这个！ - JonathanDavidArndt

Answer 2

如果您使用ASCIISTR函数将Unicode转换为形如\nnnn的文字，然后可以使用REGEXP_REPLACE将这些文字删除，如下所示...

UPDATE table SET field = REGEXP_REPLACE(ASCIISTR(field), '\\[[:xdigit:]]{4}', '')

...其中field和table分别是您的字段名和表名。

- Robb Smith

---

如果字符串的长度接近4000，则ASCIISTR（）将超出此限制扩展字符串，并且字符串将被截断为4000个字符（从末尾丢失多余字符）。[SQLFIDDLE]（http://sqlfiddle.com/#!4/224ee/13） - MT0

这也会匹配反斜杠字符，但这是不希望的，因为它是ASCII。 - rtaft

为了解决这个问题，可以编写代码 where replace(asciistr(field), asciistr('\'), '\') <> field。 - Juraj

Answer 3

我不建议将其用于生产代码，但它是有意义的并且似乎可以工作：

SELECT REGEXP_REPLACE(COLUMN,'[^' || CHR(1) || '-' || CHR(127) || '],'')

- Francisco Hayoz

---

请注意，通常应该从32开始而不是1，因为那是第一个可打印的ASCII字符。其余的是控制字符，在文本列中使用会很奇怪（比>127更奇怪）。但是从技术上讲，答案是正确的，这将检测到非ASCII字符，给定原始的7位ASCII标准。 - Luc

Answer 4

选择可能看起来像以下示例：

select nvalue from table
where length(asciistr(nvalue))!=length(nvalue)  
order by nvalue;

- Jeff Dwight

---

好主意，但是这样实际上是在识别数据字段的大小（以字节为单位）与它们所代表的符号数量不同的情况。 - lucabelluccini

也错误地将 "" 键返回为非 ASCII 字符。 - Mike Samaras

Answer 5

在单字节 ASCII 兼容编码（例如 Latin-1）中，ASCII 字符仅是在 0 到 127 范围内的字节。因此，您可以使用类似 [\x80-\xFF] 的内容来检测非 ASCII 字符。

- Max Shawabkeh

---

我尝试按建议使用十六进制代码，但是：-regexp_replace(column,'[\x00-\xFF]','')除大写字母外没有删除任何内容-- 我需要转义什么或者还有其他需要做的事情吗？ - Paul Gilfedder

我在使用你的解决方案时遇到了问题。这个答案已被接受，所以我认为它并不是完全错误的，但有两点需要注意：1）Oracle不支持使用正则表达式语法指定代码点/字符的十六进制表示形式（例如'\x80'），而是必须指定字符本身（但是，正则表达式模式是一个字符串表达式，因此您可以使用类似于'['||chr(128)||'-'||chr(255)||']'的东西），2）尝试替换所有字符 '['||chr(32)||'-'||chr(127)||']' 将导致 ora-12728 错误（正则表达式中的无效范围）。我的数据库字符集是 al32utf8。有什么想法吗？ - collapsar

我应该补充说明以下内容：1）数据库是Oracle 11.2.0.3.0，2）范围32-122、32-255不会导致错误，但是3）应用于由大小写字母和数字组成的字符串时，显示出与预期相反的行为（即REGEXP_REPLACE('abc'，'['||chr(32)||'-'||chr(128)||']'，'_' )生成abc，而REGEXP_REPLACE('abc'，'[^'||chr(32)||'-'||chr(128)||']'，'_' )返回___）。 - collapsar

Answer 6

您可以尝试以下内容来搜索包含非ASCII字符的列：

select * from your_table where your_col <> asciistr(your_col);

- Shardul Dhanorkar

Answer 7

以下内容也可以正常工作：

select dump(a,1016), a from (
SELECT REGEXP_REPLACE (
          CONVERT (
             '3735844533120%$03  ',
             'US7ASCII',
             'WE8ISO8859P1'),
          '[^!@/\.,;:<>#$%&()_=[:alnum:][:blank:]]') a
  FROM DUAL);

- Sajid

Answer 8

可能有更直接使用正则表达式的方式。有幸的话，其他人可能会提供这种方法。但是如果不需要查阅手册，我会做以下操作：

创建一个PLSQL函数来接收您的输入字符串并返回一个varchar2。

在PLSQL函数中，对您的输入进行asciistr()转换。使用PLSQL是因为它可能返回一个长于4000的字符串，而在PLSQL中可以使用32K的varchar2。

该函数将非ASCII字符转换为\xxxx表示法。因此，您可以使用正则表达式查找和删除它们。然后返回结果。

- Jim Hudson

Answer 9

我有类似的需求（避免这个丑陋的ORA-31061：XDB错误：特殊字符转义失败。），但必须保留换行符。

我尝试了一个很好的评论中提到的方法。

'[^ -~|[:space:]]'

但是我遇到了这个ORA-12728: invalid range in regular expression的问题。

但是它让我找到了解决方案：

select t.*, regexp_replace(deta, '[^[:print:]|[:space:]]', '#') from  
    (select '-   <- strangest thing here, and I want to keep line break after
-' deta from dual ) t

在我的TOAD工具中，显示为

• 将所有不在打印[:print:]或空格|[:space:]字符集中的^替换为

- J. Chomel

Answer 10

我在这里找到了答案： http://www.squaredba.com/remove-non-ascii-characters-from-a-column-255.html 该链接提供了有关从列中删除非ASCII字符的解决方案。

CREATE OR REPLACE FUNCTION O1DW.RECTIFY_NON_ASCII(INPUT_STR IN VARCHAR2)
RETURN VARCHAR2
IS
str VARCHAR2(2000);
act number :=0;
cnt number :=0;
askey number :=0;
OUTPUT_STR VARCHAR2(2000);
begin
str:=’^'||TO_CHAR(INPUT_STR)||’^';
cnt:=length(str);
for i in 1 .. cnt loop
askey :=0;
select ascii(substr(str,i,1)) into askey
from dual;
if askey < 32 or askey >=127 then
str :=’^'||REPLACE(str, CHR(askey),”);
end if;
end loop;
OUTPUT_STR := trim(ltrim(rtrim(trim(str),’^'),’^'));
RETURN (OUTPUT_STR);
end;
/

然后运行以下命令以更新您的数据。

update o1dw.rate_ipselect_p_20110505
set NCANI = RECTIFY_NON_ASCII(NCANI);

- Matt McGurie