現在、Oracleデータベースの1つをUTF8に移行していますが、4000バイトのvarchar制限に近いレコードがいくつか見つかりました。 これらのレコードを移行しようとすると、マルチバイトUF8文字になる文字が含まれているため失敗します。 PL/SQL内でこれらの文字が何であるかを確認し、変更または削除したいのです。
私がやりたいのは、:
SELECT REGEXP_REPLACE(COLUMN,'[^[:ascii:]],'')
をしたいのですが、Oracleは[:ascii:]文字クラスを実装していません。
私がしたいことをする簡単な方法はありますか?
これでうまくいくと思います。
SELECT REGEXP_REPLACE(COLUMN, '[^[:print:]]', '')
selectは次のサンプルのように見える場合があります。
select nvalue from table
where length(asciistr(nvalue))!=length(nvalue)
order by nvalue;
正規表現を使ったもっと直接的な方法があるだろう。運が良ければ、誰かがそれを提供してくれるだろう。しかし、マニュアルを見るまでもなく、私ならこうする。
入力文字列を受け取り、varchar2を返すPLSQL関数を作成します。
PLSQL関数の中で、入力をasciistr()します。PLSQLは4000より長い文字列を返す可能性があり、PLSQLのvarchar2には32Kの空きがあるからです。
この関数は、非ASCII文字をasciistr()表記に変換します。そのため、正規表現を使ってこれらの文字を見つけて取り除くことができます。そして結果を返します。
ここで答えを見つけました。
http://www.squaredba.com/remove-non-ascii-characters-from-a-column-255.html。
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);
以下を試してください。
-- 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
同じような問題があり、それについてブログに書いた こちら。 私はアルファ数字の正規表現から始めて、それから私が好きないくつかの基本的な句読点を追加した:
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;
utl_raw.cast_to_varchar2で使用できる置換したい16進文字を出すために、1016バリアントでdumpを使用しました。
いつでもご利用ください。
regexp_like(column, '[A-Z]')
Oracleのregexpエンジンは、Latin-1範囲の特定の文字にも一致します。これは、Ä-> A、Ö-> O、ÜなどのASCII文字に似ているすべての文字に適用されます;-> Uなど.つまり、[A-Z]は、たとえばPerlなどの他の環境から知っているものではありません。
正規表現をいじる代わりに、文字セットアップグレードの前にNVARCHAR2データ型に変更してみてください。
別のアプローチ:データベースにヨーロッパの文字が含まれている場合(つまり、フィールドの内容の一部を削除する代わりに、SOUNDEX関数を試すことができます。 ラテン-1)文字のみ。 または、Latin-1の範囲の文字を、類似した外観のASCII文字に変換する関数を作成するだけです。
-å => a。 -ä => a。 -ö => o。
もちろん、UTF-8に変換したときに4000バイトを超えるテキストブロックのみ。
フランシスコ・ハヨズによる答えは最高です。 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,245,246,242,242
つまり、何らかの理由で、このバージョンのOracleはchar(226)以上を置き換えません。 '[' || chr(127)|| '-' || chr(225)|| ']を使用して、望ましい結果が得られます。 他の文字を置き換える必要がある場合は、上記の正規表現に追加するか、ネストされた置換を使用します| regexp_replace置換が異なる場合は ''(null string)を使用します。
私はこの質問に答えるのに少し遅れていますが、最近同じ問題がありました(人々はあらゆる種類のものをひもに切り貼りし、それが何であるかを常に知っているわけではありません)。 以下は、単純なキャラクターホワイトリストのアプローチです。
SELECT est.clients_ref
,TRANSLATE (
est.clients_ref
, 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ01234567890#$%^&*()_+-={}|[]:";<>?,./'
|| REPLACE (
TRANSLATE (
est.clients_ref
,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ01234567890#$%^&*()_+-={}|[]:";<>?,./'
,'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
)
,'~'
)
,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ01234567890#$%^&*()_+-={}|[]:";<>?,./'
)
clean_ref
FROM edms_staging_table est。