Как заменить любой символ в строке, имеющей значение ASCII, большее 127 в Oracle [duplicate]

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

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

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

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

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

FROM edms_staging_table est

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

Typ = 1 Len = 30: 226,227,228,229,234,235,236,237,238,239,240,241,242,243,244,245,246,247,248,249,244,251,252,253,254,255

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

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

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

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

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

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

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

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

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

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

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

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

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

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.

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

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

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

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

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

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

Сделайте это, он будет работать.

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

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

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

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

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

Также работает:

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

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

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