Подтвердить что ты не робот

Поиск и удаление символов без ascii из Oracle Varchar2

В настоящее время мы переносим одну из наших баз данных оракула в UTF8, и мы обнаружили несколько записей, которые находятся рядом с пределом varchar 4000 байт. Когда мы пытаемся выполнить миграцию этих записей, они терпят неудачу, поскольку они содержат символы, которые становятся многобайтными символами UF8. То, что я хочу сделать в PL/SQL, - это найти эти символы, чтобы узнать, что они собой представляют, а затем либо изменить их, либо удалить.

Я хотел бы сделать:

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

но Oracle не реализует класс символов [: ascii:].

Есть ли простой способ делать то, что я хочу сделать?

4b9b3361

ОТВЕТЫ

Ответ 1

В однобайтовой ASCII-совместимой кодировке (например, Latin-1) символы ASCII являются просто байтами в диапазоне от 0 до 127. Таким образом, вы можете использовать что-то вроде [\x80-\xFF] для обнаружения символов, отличных от ASCII.

Ответ 2

Если вы используете функцию ASCIISTR для преобразования Юникода в литералы формы \nnnn, вы можете использовать REGEXP_REPLACE, чтобы удалить эти литералы, например...

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

... где поле и таблица являются вашими именами полей и таблиц соответственно.

Ответ 3

Я думаю, что это сделает трюк: 

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

Ответ 4

Я бы не рекомендовал его для производственного кода, но он имеет смысл и, похоже, работает:

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

Ответ 5

Вероятно, существует более прямой способ использования регулярных выражений. Если повезет, кто-то еще это предоставит. Но вот что я сделал бы, не обращаясь к руководствам.

Создайте функцию PLSQL для получения введенной строки и верните varchar2.

В функции PLSQL выполните asciistr() вашего ввода. PLSQL заключается в том, что он может возвращать строку длиной более 4000 и у вас есть 32K для varchar2 в PLSQL.

Эта функция преобразует символы не-ASCII в нотацию \xxxx. Поэтому вы можете использовать регулярные выражения для их поиска и удаления. Затем верните результат.

Ответ 6

Выбор может выглядеть следующим образом:

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

Ответ 7

Также работает следующее:

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

Ответ 8

У меня была аналогичная проблема и я писал об этом здесь. Я начал с регулярного выражения для альфа-чисел, а затем добавил в несколько основных символов пунктуации, которые мне нравились:

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;

Я использовал дамп с вариантом 1016, чтобы выдать шестнадцатеричные символы, которые я хотел заменить, которые я мог бы затем использовать в utl_raw.cast_to_varchar2.

Ответ 9

Я нашел ответ здесь:

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

Ответ 10

Попробуйте следующее:

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

Ответ 11

Ответ, данный Франциско Хайозом, является лучшим. Не используйте функции pl/sql, если 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"

Тип = 1 Лен = 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

i.e по какой-то причине эта версия Oracle не заменяет char (226) и выше. Используя '[' || chr (127) || '-' || chr (225) || ']' дает желаемый результат. Если вам нужно заменить другие символы, просто добавьте их в регулярное выражение выше или используйте вложенную замену | regexp_replace, если замена отличается от "'(пустая строка).

Ответ 12

Спасибо, это сработало для моих целей. Кстати, в приведенном выше примере отсутствует одиночная кавычка.

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

Я использовал его в функции переноса слов. Иногда во входящем тексте была встроена новая строка /NL/CHR (10)/0A, которая была беспорядочной.

Ответ 13

Обратите внимание, что всякий раз, когда вы используете

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

Oracle regexp engine будет соответствовать некоторым символам из диапазона Latin-1: это относится ко всем символам, которые похожи на символы ASCII, такие как Ä- > A, Ö- > O, Ü- > U и т.д., поэтому что [AZ] не то, что вы знаете из других сред, например, Perl.

Вместо того, чтобы возиться с регулярными выражениями, попробуйте изменить тип данных NVARCHAR2 до обновления набора символов.

Другой подход: вместо того, чтобы отсекать часть содержимого полей, вы можете попробовать функцию SOUNDEX, если ваша база данных содержит только символы в Европе (например, латинские-1). Или вы просто пишете функцию, которая переводит символы из диапазона Latin-1 в аналогичные ASCII-символы, например

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

конечно, только для текстовых блоков, превышающих 4000 байтов при преобразовании в UTF-8.

Ответ 14

Вы можете попробовать что-то вроде следующего, чтобы найти столбец, содержащий символ не-ascii:

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

Ответ 15

Сделайте это, это сработает.

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

Ответ 16

Я немного опоздал с ответом на этот вопрос, но у меня была такая же проблема в последнее время (люди вырезают и вставляют всевозможные вещи в строку, и мы не всегда знаем, что это такое). Ниже приведен простой подход белого списка:

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

FROM edms_staging_table est