Individuazione e rimozione di caratteri non ascii da un Oracle Varchar2

Stiamo attualmente migrando uno dei nostri database Oracle a UTF8 e abbiamo trovato alcuni record vicini al limite di 4000 byte varchar. Quando proviamo a migrare questi record, falliscono poiché contengono caratteri che diventano caratteri UF8 multibyte. Quello che voglio fare in PL / SQL è individuare questi caratteri per vedere cosa sono e quindi cambiarli o rimuoverli.

Mi piacerebbe fare :

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

ma Oracle non implementa la class di caratteri [: ascii:].

C’è un modo semplice per fare ciò che voglio fare?

In una codifica ASCII compatibile con un singolo byte (ad es. Latin-1), i caratteri ASCII sono semplicemente byte nell’intervallo da 0 a 127. Quindi è ansible utilizzare qualcosa come [\x80-\xFF] per rilevare caratteri non ASCII.

Se utilizzi la funzione ASCIISTR per convertire l’Unicode in letterali del modulo \nnnn , puoi utilizzare REGEXP_REPLACE per REGEXP_REPLACE quei letterali, in questo modo …

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

… dove campo e tabella sono rispettivamente i nomi dei campi e dei tavoli.

Penso che questo farà il trucco:

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

Non lo consiglierei per il codice di produzione, ma ha senso e sembra funzionare:

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

C’è probabilmente un modo più diretto con le espressioni regolari. Con un po ‘di fortuna, qualcun altro lo fornirà. Ma ecco cosa farei senza dover consultare i manuali.

Creare una funzione PLSQL per ricevere la stringa di input e restituire un varchar2.

Nella funzione PLSQL, fai un asciistr () del tuo input. Il PLSQL è perché questo può restituire una stringa più lunga di 4000 e hai 32K disponibili per varchar2 in PLSQL.

Quella funzione converte i caratteri non ASCII nella notazione \ xxxx. Quindi puoi usare le espressioni regolari per trovare e rimuovere quelle. Quindi restituire il risultato.

Quanto segue funziona anche:

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

La selezione può apparire come il seguente esempio:

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

Ho avuto un problema simile e ne ho parlato qui . Ho iniziato con l’espressione regolare per i caratteri alfa, poi ho aggiunto i pochi caratteri di punteggiatura di base che mi piacevano:

 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; 

Ho usato il dump con la variante 1016 per distribuire i caratteri esadecimali che volevo sostituire, che potevo quindi usare in un utl_raw.cast_to_varchar2.

Ho trovato la risposta qui:

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; / 

Quindi esegui questo per aggiornare i tuoi dati

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

Prova quanto segue:

 -- 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 

La risposta data da Francisco Hayoz è la migliore. Non utilizzare le funzioni pl / sql se sql può farlo per te.

Ecco il semplice test in 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)) 

E "rappresentante 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

vale a dire per qualche ragione questa versione di Oracle non sostituisce char (226) e sopra. Usando '[' || chr (127) || '-' || chr (225) || ']' dà il risultato desiderato. Se è necessario sostituire altri caratteri, aggiungili alla regex precedente o usa replace | regexp_replace se la sostituzione è diversa da "" (stringa nulla).

Grazie, ha funzionato per i miei scopi. A proposito c’è una virgoletta singola mancante nell’esempio, sopra.

REGEXP_REPLACE (COLUMN, ‘[^’ || CHR (32) || ‘-‘ || CHR (127) || ‘]’, ”))

L’ho usato in una funzione a capo automatico. Di tanto in tanto c’era una NewLine / NL / CHR (10) / 0A incorporata nel testo in entrata che faceva casino.

Si prega di notare che ogni volta che si utilizza

 regexp_like(column, '[AZ]') 

Il motore regexp di Oracle abbinerà alcuni caratteri del range Latin-1: questo vale per tutti i caratteri che somigliano ai caratteri ASCII come Ä-> A, Ö-> O, Ü-> U, ecc., In modo che [AZ ] non è quello che sai da altri ambienti come, per esempio, Perl.

Invece di giocherellare con le espressioni regolari, provare a cambiare per il tipo di dati NVARCHAR2 prima dell’aggiornamento del set di caratteri.

Un altro approccio: invece di eliminare parte dei contenuti dei campi, è ansible provare la funzione SOUNDEX, a condizione che il database contenga solo caratteri europei (cioè Latin-1). O scrivi semplicemente una funzione che traduce i caratteri dalla gamma Latin-1 in caratteri ASCII simili, come

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

ovviamente solo per blocchi di testo che superano i 4000 byte quando vengono trasformati in UTF-8.

Puoi provare qualcosa come seguire per cercare la colonna che contiene caratteri non ascii:

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

Fai questo, funzionerà.

 trim(replace(ntwk_slctor_key_txt, chr(0), '')) 

Sono un po ‘in ritardo nel rispondere a questa domanda, ma ho avuto lo stesso problema di recente (la gente taglia e incolla tutti i tipi di cose in una stringa e non sempre sappiamo cosa sia). Quello che segue è un approccio di whitelist per i caratteri semplici:

 SELECT est.clients_ref ,TRANSLATE ( est.clients_ref , 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ01234567890#$%^&*()_+-={}|[]:";<>?,./' || REPLACE ( TRANSLATE ( est.clients_ref ,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ01234567890#$%^&*()_+-={}|[]:";<>?,./' ,'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~' ) ,'~' ) ,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ01234567890#$%^&*()_+-={}|[]:";<>?,./' ) clean_ref 

DA edms_staging_table est