regex - regular - Encontrar y eliminar caracteres no ascii de un Oracle Varchar2

quitar caracteres extraños oracle

Question

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?

Answer 1

Creo que esto hará el truco:

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

Answer 2

En una codificación 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.

Answer 3

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

Answer 4

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.

Answer 5

Haz esto, funcionará.

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

Answer 6

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 anidado si la sustitución es diferente a '''' (cadena nula).

Answer 7

La selección puede parecerse a la siguiente muestra:

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

Answer 8

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

Answer 9

Lo siguiente también funciona:

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

Answer 10

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) || ''],'''')

Answer 11

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.

Answer 12

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

Answer 13

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

Answer 14

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.

Answer 15

Tenga en cuenta que siempre que use

regexp_like(column, ''[A-Z]'')

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.

Answer 16

Tuve un problema similar y escribí en el blog here . 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.