从Oracle Varchar2查找和删除非ASCII字符 [英] Finding and removing non ascii characters from an Oracle Varchar2

问题描述

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

We are currently migrating one of our oracle databases to UTF8 and we have found a few records that are near the 4000 byte varchar limit. When we try and migrate these record they fail as they contain characters that become multibyte UF8 characters. What I want to do within PL/SQL is locate these characters to see what they are and then either change them or remove them.

我想做:

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

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

but Oracle does not implement the [:ascii:] character class.

有一种简单的方法可以做我想做的事吗?

Is there a simple way doing what I want to do?

推荐答案

在单字节ASCII兼容编码(例如Latin-1)中，ASCII字符只是0到127范围内的字节.因此，您可以使用一些东西类似于[\x80-\xFF]来检测非ASCII字符.

In a single-byte ASCII-compatible encoding (e.g. Latin-1), ASCII characters are simply bytes in the range 0 to 127. So you can use something like [\x80-\xFF] to detect non-ASCII characters.

这篇关于从Oracle Varchar2查找和删除非ASCII字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！