Encontrar y eliminar caracteres no ascii de un Oracle Varchar2

Actualmente estamos migrando una de nuestras bases de datos de Oracle a UTF8 y hemos encontrado algunos registros que están cerca del límite de varchar de 4000 bytes. Cuando intentamos migrar estos registros, fallan ya que contienen caracteres que se convierten en caracteres UF8 de varios bytes. Lo que quiero hacer dentro de PL / SQL es localizar estos personajes para ver cuáles son y luego cambiarlos o eliminarlos.

Me gustaría hacer :

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

pero Oracle no implementa la clase de caracteres [: ascii:].

¿Hay una manera simple de hacer lo que quiero hacer?

En una encoding compatible con ASCII de un solo byte (por ejemplo, Latin-1), los caracteres ASCII son simplemente bytes en el rango de 0 a 127. Por lo tanto, puede usar algo como [\x80-\xFF] para detectar caracteres que no sean ASCII.

Si usa la función ASCIISTR para convertir el Unicode a literales de la forma \nnnn , puede usar REGEXP_REPLACE para quitar esos literales, como ese …

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

… donde el campo y la tabla son sus nombres de campo y tabla, respectivamente.

Creo que esto hará el truco:

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

No lo recomendaría para el código de producción, pero tiene sentido y parece funcionar:

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

Probablemente haya una forma más directa de usar expresiones regulares. Con suerte, alguien más lo proporcionará. Pero esto es lo que haría sin tener que ir a los manuales.

Cree una función PLSQL para recibir su cadena de entrada y devolver un varchar2.

En la función PLSQL, haga un asciistr () de su entrada. El PLSQL se debe a que puede devolver una cadena de más de 4000 y tiene 32K disponible para varchar2 en PLSQL.

Esa función convierte los caracteres que no son ASCII a la notación \ xxxx. Entonces puedes usar expresiones regulares para encontrarlas y eliminarlas. Luego regresa el resultado.

Lo siguiente también funciona:

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

La selección puede parecerse a la siguiente muestra:

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

Tuve un problema similar y escribí en el blog aquí . Comencé con la expresión regular de los números alfa numéricos, luego agregué los pocos caracteres de puntuación básicos que me gustaron:

 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; 

Utilicé el volcado con la variante 1016 para dar los caracteres hexadecimales que quería reemplazar y que luego pude usar en utl_raw.cast_to_varchar2.

Encontré la respuesta aquí:

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

Luego ejecuta esto para actualizar tus datos

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

Pruebe lo siguiente:

 -- 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 respuesta dada por Francisco Hayoz es la mejor. No use las funciones pl / sql si sql puede hacerlo por usted.

Aquí está la prueba simple en 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)) 

Y "rep 127-255" es

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

es decir, por alguna razón, esta versión de Oracle no reemplaza a char (226) y superior. Usando '[' || chr (127) || '-' || chr (225) || ']' da el resultado deseado. Si necesita reemplazar otros caracteres, simplemente agréguelos a la expresión regular anterior o utilice replace | regexp_replace nested si la sustitución es diferente a '' (cadena nula).

Gracias, esto funcionó para mis propósitos. Por cierto, falta una comilla simple en el ejemplo anterior.

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

Lo usé en una función de ajuste de palabras. De vez en cuando había un NewLine / NL / CHR incrustado (10) / 0A en el texto entrante que estaba estropeando las cosas.

Tenga en cuenta que siempre que use

 regexp_like(column, '[AZ]') 

El motor regexp de Oracle también coincidirá con ciertos caracteres del rango Latin-1: esto se aplica a todos los caracteres que se parecen a los caracteres ASCII como Ä-> A, Ö-> O, Ü-> U, etc., de modo que [AZ ] no es lo que sabes de otros entornos como, por ejemplo, Perl.

En lugar de jugar con expresiones regulares, intente cambiar por el tipo de datos NVARCHAR2 antes de la actualización del juego de caracteres.

Otro enfoque: en lugar de cortar parte del contenido de los campos, puede probar la función SOUNDEX, siempre que su base de datos contenga solo caracteres europeos (es decir, Latin-1). O simplemente escribe una función que traduce caracteres de la gama Latin-1 en caracteres ASCII de aspecto similar, como

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

por supuesto, solo para bloques de texto que superan los 4000 bytes cuando se transforman en UTF-8.

Puede intentar algo como lo siguiente para buscar la columna que contiene caracteres no ascii:

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

Haz esto, funcionará.

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

Llegué un poco tarde a responder esta pregunta, pero recientemente tuve el mismo problema (las personas cortan y pegan todo tipo de cosas en una cadena y no siempre sabemos de qué se trata). El siguiente es un enfoque de lista blanca de caracteres simple:

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

DESDE edms_staging_table est