logo标签列表

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

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

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

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

您可能希望将 ç 替换为 c 等。丢弃整个字符比丢弃变音符号更糟糕。 - MSalters
2
我们首先需要弄清楚这些字符是什么，然后再决定如何处理它们。 - Paul Gilfedder
这是一个破坏性的过程，您是否想要保留一些字符的ASCII替换？例如破折号、单引号、双引号等等。unistr 0013-, 0018'，0019'，001C"，001D"。 - Sun
18个回答
2

我遇到过类似的问题，曾经在这里博客中写过关于它的内容。 我从匹配字母数字的正则表达式开始，然后加入了几个我喜欢的基本标点符号：

select dump(a,1016), a, b
from
 (select regexp_replace(COLUMN,'[[:alnum:]/''%()> -.:=;[]','') a,
         COLUMN b
  from TABLE)
where a is not null
order by a;

我使用了1016变体的dump函数来提取我想要替换的十六进制字符，然后可以在utl_raw.cast_to_varchar2中使用。

2
请尝试以下方法：
-- To detect
select 1 from dual
where regexp_like(trim('xx test text æ¸¬è© ¦ “xmx” number²'),'['||chr(128)||'-'||chr(255)||']','in')

-- To strip out
select regexp_replace(trim('xx test text æ¸¬è© ¦ “xmxmx” number²'),'['||chr(128)||'-'||chr(255)||']','',1,0,'in')
from dual
1
感谢，这对我的目的很有用。另外，上面的示例中缺少一个单引号。
REGEXP_REPLACE (COLUMN,'[^' || CHR (32) || '-' || CHR (127) || ']', ' '))

我在一个自动换行的函数中使用它。偶尔会有嵌入的NewLine/NL/CHR(10)/0A在输入的文本中，这会导致问题。

0
请注意，无论何时使用

都要确保在相应的位置上使用它。
regexp_like(column, '[A-Z]')

Oracle的正则表达式引擎也会匹配拉丁-1范围内的某些字符：这适用于所有看起来类似于ASCII字符的字符，例如Ä->A，Ö->O，Ü->U等，因此[A-Z]不是您从其他环境（如Perl）中所知道的。

与其纠结于正则表达式，不如在字符集升级之前将数据类型更改为NVARCHAR2。

另一种方法：而不是削减字段内容的一部分，您可以尝试使用SOUNDEX函数，前提是您的数据库仅包含欧洲字符（即拉丁-1字符）。或者您可以编写一个函数，将拉丁-1范围内的字符转换为外观相似的ASCII字符，例如

  • å => a
  • ä => a
  • ö => o

当转换为UTF-8后，当然只适用于超过4000字节的文本块。

0

正如此评论和此评论中所指出的，您可以使用范围。
在使用Oracle 11时，以下方法非常有效：

SELECT REGEXP_REPLACE(dummy, '[^ -~|[:space:]]', '?') AS dummy FROM DUAL;

这将把可打印范围以外的任何内容替换为问号。

此代码将直接运行，因此您可以验证其语法与您的安装是否兼容。
请将dummy和dual替换为您自己的列/表。

0

Francisco Hayoz 给出的答案是最好的。如果 SQL 可以胜任，就不要使用 PL/SQL 函数。

以下是在 Oracle 11.2.03 中进行的简单测试。

select s
     , regexp_replace(s,'[^'||chr(1)||'-'||chr(127)||']','') "rep ^1-127"
     , dump(regexp_replace(s,'['||chr(127)||'-'||chr(225)||']','')) "rep 127-255"
from (
select listagg(c, '') within group (order by c) s
  from (select 127+level l,chr(127+level) c from dual connect by level < 129))

"rep 127-255" 是

Typ=1 Len=30: 226,227,228,229,230,231,232,233,234,235,236,237,238,239,240,241,242,243,244,245,246,247,248,249,250,251,252,253,254,255

即，由于某些原因，此版本的Oracle不会替换char（226）及以上字符。 使用'[' || chr（127）||'-'||chr（225）||']' 将获得所需结果。 如果您需要替换其他字符，请将它们添加到上面的正则表达式中或者如果替换不同于''（空字符串），则使用嵌套的replace|regexp_replace。

-2

做这个，它会起作用。

trim(replace(ntwk_slctor_key_txt, chr(0), ''))
1
欢迎来到Stack Overflow！这个答案出现在低质量审阅队列中，可能是因为您没有解释代码。如果您解释一下（在您的答案中），则更有可能获得更多的赞，并且提问者更有可能学到东西！ - The Guy with The Hat
-3
我回答这个问题有点晚，但最近也遇到了同样的问题（人们把各种各样的东西复制粘贴到一个字符串中，而我们并不总是知道它是什么）。 以下是一种简单的字符白名单方法：
SELECT est.clients_ref
  ,TRANSLATE (
              est.clients_ref
             ,   'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ01234567890#$%^&*()_+-={}|[]:";<>?,./'
              || REPLACE (
                          TRANSLATE (
                                     est.clients_ref
                                    ,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ01234567890#$%^&*()_+-={}|[]:";<>?,./'
                                    ,'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
                                    )
                         ,'~'
                         )
             ,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ01234567890#$%^&*()_+-={}|[]:";<>?,./'
             )
      clean_ref

从edms_staging_table est

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接