Удалить спецсимволы из строки oracle
в настоящее время мы переносим одну из наших баз данных oracle в UTF8, и мы нашли несколько записей, которые близки к пределу 4000 байт varchar. Когда мы пытаемся перенести эти записи, они терпят неудачу, поскольку они содержат символы, которые становятся многобайтовыми символами UF8. Что я хочу сделать в PL / SQL, это найти эти символы, чтобы увидеть, что они такое, а затем либо изменить их, либо удалить.
Я хотел бы сделать :
но Oracle не реализует [: ascii:] класс символов.
есть ли простой способ сделать то, что я хочу сделать?
в однобайтовой кодировке, совместимой с ASCII (например, Latin-1), символы ASCII-это просто байты в диапазоне от 0 до 127. Так что вы можете использовать что-то вроде [\x80-\xFF] для обнаружения символов, отличных от ASCII.
если вы используете ASCIISTR функция для преобразования Юникода в литералы формы \nnnn , тогда вы можете использовать REGEXP_REPLACE чтобы удалить эти литералы, вот так.
. где field и table-ваши имена полей и таблиц соответственно.
Я думаю, что это будет делать трюк:
Я бы не рекомендовал его для производственного кода, но это имеет смысл и, кажется, работает:
вероятно, есть более прямой способ использования регулярных выражений. Если повезет, кто-нибудь другой обеспечит его. Но вот что я бы сделал без необходимости обращаться к руководствам.
создайте функцию PLSQL для получения входной строки и возврата varchar2.
в функции PLSQL выполните asciistr () вашего ввода. PLSQL потому, что это может вернуть строку длиннее 4000, и у вас есть 32K, доступный для varchar2 в PLSQL.
эта функция преобразует символы, отличные от ASCII, для обозначения \xxxx. Так что вы можете использовать регулярные выражения, чтобы найти и удалить их. Затем верните результат.
выбор может выглядеть следующим образом:
У меня была аналогичная проблема, и я написал об этом в блоге здесь. Я начал с регулярного выражения для Альфа-цифр, затем добавил несколько основных знаков препинания, которые мне понравились:
я использовал дамп с вариантом 1016, чтобы выдать шестнадцатеричные символы, которые я хотел заменить, которые я мог бы использовать в utl_raw.cast_to_varchar2.
Я нашел ответ здесь:
затем запустите это, чтобы обновить данные
ответ, данный Франсиско Hayoz является лучшим. Не используйте функции pl/sql, если sql может сделать это за вас.
вот простой тест в Oracle 11.2.03
и "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)//'] ' дает желаемый результат. Если вам нужно заменить другие символы, просто добавьте их в регулярное выражение выше или используйте вложенную replace|regexp_replace, если замена отличается " (нулевая строка).
Спасибо, это работает для моих целей. Кстати, в приведенном выше примере отсутствует одинарная кавычка.
REGEXP_REPLACE (СТОЛБЕЦ, ' [^'|/CHR (32) || '-' || CHR (127) || ']', ' '))
Я использовал его в функции переноса слов. Иногда во входящем тексте была встроенная новая строка/ NL / CHR(10) / 0A, которая все испортила.
обратите внимание, что всякий раз, когда вы используете
механизм регулярных выражений Oracle будет соответствовать определенным символам из диапазона Latin-1: это относится ко всем символам, которые похожи на символы ASCII, такие как Ä->A, Ö->O, Ü->U и т. д., так что [A-Z] - это не то, что вы знаете из других сред, таких как, скажем, Perl.
вместо того, чтобы возиться с регулярными выражениями, попробуйте изменить тип данных NVARCHAR2 до обновления набора символов.
другой подход: вместо того, чтобы вырезать часть содержимого полей, вы можете попробовать функцию SOUNDEX, при условии, что ваша база данных содержит только европейские символы (т. е. латинские-1). Или вы просто пишете функцию, которая переводит символы из диапазона Latin-1 в похожие символы ASCII, такие как
конечно только для текстовых блоков, превышающих 4000 байт при преобразовании в UTF-8.
вы можете попробовать что-то вроде следующего для поиска столбца, содержащего символ, отличный от ascii :
Регулярные выражения произошли из теорий автоматов и формальных языков, поэтому поначалу производят устрашающее впечатление. Однако, их базовые понятия являются простыми и в то же время мощными.
Начиная с версии Oracle 10g регулярные выражения можно использовать напрямую в SQL запросах.
Шаблон регулярных выражений выражается в виде строки, содержащей следующие конструкции:
- Литеральные символы. Фактические символы, которые следует искать (Например, шаблон xyz соответствует только вхождению «xyz»)
- Метасимволы. Операции, определяющие алгоритмы, которые должны применяться во время поиска (Например, шаблон ^xyz соответсвует только строке, начинающейся с «xyz» — другие вхождения не учитываются)
Оператор REGEXP_LIKE
REGEXP_LIKE(исходная_строка, шаблон[, параметр_сопоставления])
исходная_строка | поддерживает символьные типы данных (CHAR, VARCHAR2, CLOB, NCHAR, NVARCHAR2 и NCLOB, но не LONG) |
шаблон | это другое название регулярного выражения |
параметр_сопоставления | позволяет использовать дополнительные параметры, такие как символ перехода на новую строку, многострочное форматирование и обеспечение управления учетом регистра |
Используется подобно оператору like в части where или же при определении ограничения на таблицу (constraint) .
Пример использования регулярных выражений:
Функция REGEXP_INSTR
REGEXP_INSTR(исходная_строка, шаблон[, начальная_позиция [, вхождение [, опция_возврата [, параметр_сопоставления ] ] ] ] )
исходная_строка | поддерживает символьные типы данных (CHAR, VARCHAR2, CLOB, NCHAR, NVARCHAR2 и NCLOB, но не LONG) |
шаблон | регулярное выражение |
начальная_позиция | позиция, с которой должен начинаться поиск |
вхождение | по умолчанию имеет значение 1, если пользователь не укажет поиск последовательных вхождений |
опция_возврата | значение по умолчанию 0, тогда возвратится начальная позиция шаблона; при значении 1 возвращается позиция символа, следующего за шаблоном |
параметр_сопоставления | позволяет использовать дополнительные параметры, такие как символ перехода на новую строку, многострочное форматирование и обеспечение управления учетом регистра |
Функция возвращает позицию символа, находящегосяв начале или конце соответствия для шаблона, так же как и ее аналог instr.
В отличие от instr, функция regexp_instr работает с начала строки и двигается вперед в поисках шаблона. Она не может начать с конца строки и перемещаться в обратном направлении.
Функция REGEXP_SUBSTR
REGEXP_SUBSTR(исходная_строка, шаблон[, позиция [, вхождение [,параметр_сопоставления]]])
исходная_строка | поддерживает символьные типы данных (CHAR, VARCHAR2, CLOB, NCHAR, NVARCHAR2 и NCLOB, но не LONG) |
шаблон | регулярное выражение |
позиция | позиция, с которой необходимо начинать поиск |
вхождение | по умолчанию имеет значение 1 |
параметр_сопоставления | позволяет использовать дополнительные параметры, такие как символ перехода на новую строку, многострочное форматирование и обеспечение управления учетом регистра |
Функция REGEXP_SUBSTR возвращает подстроку, которая соответствует шаблону.
Пример использования регулярных выражений:
Функция REGEXP_REPLACE
REGEXP_REPLACE(исходная_строка, шаблон [, строка_замены [, позиция[,вхождение, [параметр_сопоставления]]]])
исходная_строка | поддерживает символьные типы данных (CHAR, VARCHAR2, CLOB, NCHAR, NVARCHAR2 и NCLOB, но не LONG) |
шаблон | регулярное выражение |
шаблон замены | текст для замены каждого вхождения |
позиция | позиция, с которой необходимо начинать поиск |
вхождение | по умолчанию имеет значение 1 |
параметр_сопоставления | позволяет использовать дополнительные параметры, такие как символ перехода на новую строку, многострочное форматирование и обеспечение управления учетом регистра |
REGEXP_REPLACE возвращает измененную входную строку, в которой все вхождения шаблона заменены значением, переданным в параметре строка_замены.
Пример использования регулярных выражений:
Регулярные выражения Oracle
Таблица 1: Метасимволы привязки
Метасимвол | Описание | Пример |
^ | Привязать выражение к началу строки | «^привет» соответствует «привет, как дела», но не «как дела, привет» |
$ | Привязать выражение к концу строки | «привет$» соответсвует «как дела, привет», но не «привет, как дела» |
Таблица 2: Квантификаторы и операторы повтора
Квантификатор | Описание | Пример |
* | Встречается 0 и более раз | REGEXP_REPLACE(str, ’11*’, ‘1’) Результат: test11 => test1 11123345 => 123345 |
? | Встречается 0 или 1 раз | |
+ | Встречается 1 и более раз | REGEXP_LIKE(str,’5+’) Результат: test11 => false 11123345 => true |
Встречается ровно m раз | REGEXP_LIKE(str,’3’) Результат: test11 => false 11123345 => true | |
Встречается по крайней мере m раз | ||
Встречается по крайней мере m раз, но не более n раз |
Таблица 3: Предопределенные символьные классы POSIX
Класс символов | Описание |
. | Любой символ |
[:alpha:] | Буквы |
[:lower:] | Буквы в нижнем регистре |
[:upper:] | Буквы в верхнем регистре |
[:digit:] | Цифры |
[:alnum:] | Буквы и цифры |
[:space:] | Пробелы (не печатаемые символы), такие как перевод каретки, новая строка, вертикальная табуляция и подача страницы |
[:punct:] | Знаки препинания |
[:cntrl:] | Управляющие символы (не печатаемые) |
[:print:] | Печатаемые символы |
Таблица 4: Альтернативное сопоставление и группировка выражений
Метасимвол | Описание | |
| | Альтернатива | Разделяет альтернативные варианты, часто используется с оператором группировки () |
( ) | Группа | Группирует подвыражения для альтернативы, квантификатора или ссылочности |
[char] | Список символов | Обозначает список символов; большинство метасимволов в списке символов представляют собой литеры, за исключением символьных классов и метасимволов ^ и — |
[^char] | Список символов | Список символов, которые не должны присутствовать в строке |
Таблица 5: Метасимвол ссылки
За ней следует цифра от 1 до 9, обратная косая черта связана с предыдущим сопоставлением с соответствующим номером заключенного в скобки подвыражения.
Более полную информацию можно прочитать здесь Using Regular Expressions in Oracle Database
We are currently migrating one of our oracle databases to UTF8 and we have found a few records that are near the 4000 byte varchar limit. When we try and migrate these record they fail as they contain characters that become multibyte UF8 characters. What I want to do within PL/SQL is locate these characters to see what they are and then either change them or remove them.
I would like to do :
but Oracle does not implement the [:ascii:] character class.
Is there a simple way doing what I want to do?
You'd probably want to replace ç by c etcetera. Throwing the entire character away is worse than discarding the diacritical mark.
This is a destructive process and would you want to preserve with ascii replacements of some characters? dashes, single quotes, double quotes, etc? unistr 0013 -, 0018 ', 0019 ', 001C ", 001D "
18 Answers 18
I think this will do the trick:
This is neat and works well. Just as an adendum you can also use REGEXP_REPLACE(Column,'[^ -~]','') rather than all those Chr() functions and string concatenations mentioned above.
@Ciaran: REGEXP_REPLACE(Column,'[^ -~]','') is excellent, since Oracle doesn't support '[\x80-\xFF]' . Should be in an answer.
This is a good start, but there are plenty of characters in the "print" class that are not found/removed. This definitely got me going down the right track, so thank you for adding this!
If you use the ASCIISTR function to convert the Unicode to literals of the form \nnnn , you can then use REGEXP_REPLACE to strip those literals out, like so.
. where field and table are your field and table names respectively.
If the length of the string is close to 4000 then ASCIISTR() will extend the string beyond this limit and the string will be truncated to 4000 characters (losing the excess characters from the end). SQLFIDDLE
I wouldn't recommend it for production code, but it makes sense and seems to work:
Note that you should normally start at 32 instead of 1, since that is the first printable ascii character. The rest are control characters, which would be weird inside text columns (even weirder than >127 I'd say). But yeah technically the answer is correct, this would detect non-ascii characters, given the original 7-bit ascii standard.
In a single-byte ASCII-compatible encoding (e.g. Latin-1), ASCII characters are simply bytes in the range 0 to 127. So you can use something like [\x80-\xFF] to detect non-ASCII characters.
I tried using the hex codes as suggested however:- regexp_replace(column,'[\x00-\xFF]','') Removes nothing by the Capital letters -- do I have escape something or is there something else I need to do?
I run into issues using your solution. This answer has been accepted so I believe it is not outright wrong but 1.) oracle does not support the regex syntax to specify code points/characters by their hex representation (ie.'\x80'); instead you have to specify the characters themselves ( however, the regex pattern is a string expression so you may use something like '['||chr(128)||'-'||chr(255)||']' ), 2.) trying to replace all chars in '['||chr(32)||'-'||chr(127)||']' results in an ora-12728 error (invalid range in regex). my db charset is al32utf8. any ideas?
I should add that 1.) the DB is oracle 11.2.0.3.0, 2.) the ranges 32-122, 32-255 do not cause the error but 3.) applied to a string composed of mixed-case alphabet letters and digits show inverse behaviour to what you expect (ie. REGEXP_REPLACE ( 'abc', '['||chr(32)||'-'||chr(128)||']' , '_' ) produces abc , while REGEXP_REPLACE ( 'abc', '[^'||chr(32)||'-'||chr(128)||']' , '_' ) returns ___ ).
The select may look like the following sample:
Good idea, but with this you are actually identifying fields having data where the size in bytes is not the same of the number of the symbols represented by them.
There's probably a more direct way using regular expressions. With luck, somebody else will provide it. But here's what I'd do without needing to go to the manuals.
Create a PLSQL function to receive your input string and return a varchar2.
In the PLSQL function, do an asciistr() of your input. The PLSQL is because that may return a string longer than 4000 and you have 32K available for varchar2 in PLSQL.
That function converts the non-ASCII characters to \xxxx notation. So you can use regular expressions to find and remove those. Then return the result.
The following also works:
I had a similar issue and blogged about it here. I started with the regular expression for alpha numerics, then added in the few basic punctuation characters I liked:
I used dump with the 1016 variant to give out the hex characters I wanted to replace which I could then user in a utl_raw.cast_to_varchar2.
Then run this to update your data
Try the following:
I had similar requirement (to avoid this ugly ORA-31061: XDB error: special char to escaped char conversion failed. ), but had to keep the line breaks.
I tried this from an excellent comment
but it lead me to my solution:
displays (in my TOAD tool) as
- replace all that ^ => is not in the sets (of printing [:print:] or space |[:space:] chars)
You can try something like following to search for the column containing non-ascii character :
Answer given by Francisco Hayoz is the best. Don't use pl/sql functions if sql can do it for you.
Here is the simple test in Oracle 11.2.03
And "rep 127-255" is
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
i.e for some reason this version of Oracle does not replace char(226) and above. Using '['||chr(127)||'-'||chr(225)||']' gives the desired result. If you need to replace other characters just add them to the regex above or use nested replace|regexp_replace if the replacement is different then '' (null string).
Please note that whenever you use
Oracle's regexp engine will match certain characters from the Latin-1 range as well: this applies to all characters that look similar to ASCII characters like Ä->A, Ö->O, Ü->U, etc., so that [A-Z] is not what you know from other environments like, say, Perl.
Instead of fiddling with regular expressions try changing for the NVARCHAR2 datatype prior to character set upgrade.
Another approach: instead of cutting away part of the fields' contents you might try the SOUNDEX function, provided your database contains European characters (i.e. Latin-1) characters only. Or you just write a function that translates characters from the Latin-1 range into similar looking ASCII characters, like
of course only for text blocks exceeding 4000 bytes when transformed to UTF-8.
В посте рассматриваются однострочные функции SUBSTR и INSTR, работающие с символьными данными.
Символьные данные или строки являются универсальными, т.к. они позволяют хранить практически любой тип данных. Функции, которые работают с символьными данными, классифицируются на функции преобразования регистра символов и манипулирования символами.
Функции манипулирования символами используются для извлечения, преобразования и форматирования символьных строк. К этому классу относятся функции CONCAT, LENGTH, LPAD, RPAD, TRIM, REPLACE и рассматриваемые нижу функции SUBSTR и INSTR.
Функция SUBSTR принимает три параметра и возвращает строку, состоящую из количества символов, извлеченных из исходной строки, начиная с указанной начальной позиции:
SUBSTR (строка, начальная позиция, количество символов).
В приведенном примере извлекаются символы с первой по четвертую позиции из значений колонки last_name. Для сравнения выводятся исходные значения колонки last_name.
Функция INSTR возвращает число, представляющее позицию в исходной строке, начиная с заданной начальной позиции, где n-ное вхождение элемента поиска начинается:
INSTR (строка, элемент поиска, [начальная позиция], [n-ное вхождение элемента поиска]
Следующий запрос показывает позицию строчной буквы a для каждой строки колонки last_name. Если в строке встречаются два или более символов a, то будет отображена позиция первого/начального из них. Для сравнения и анализа выводятся исходные значения колонки.
Если необходимо также отобразить позицию заглавной буквы А в фамилии, то надо предварительно перевести все символы фамилии в строчные, используя вложенную функцию LOWER. Запрос выглядит следующим образом:
Как видно из результата, теперь позиция заглавной буквы A тоже определяется, например, для Abel, Ande, Atkinson, Austin возвращается значение 1.
В посте приведен пример совместного применения таких функций, как LENGTH, SUBSTR и INSTR.
Дядю Тома спросили про удаление специальных символов
Как удалить из строки все специальные символы? Я не знаю встроенной функции Oracle, которая позволила бы это сделать. Есть ли пакеты, которые могут в этом помочь? Я хочу удалить все символы, кроме A-Z, a-z, 0-9.
Спасибо за помощь,
Лиза
и он ответил
Такой встроенной функции нет, но можно создать свою. Например, так:
Очень хорошо, но. Комментарий Эрика, 17 мая 2002 года
Мне непонятно, почему весь этот код не помещен в функцию. Несколько странно.
Ответ дяди Тома
Просто мне нужна была строка символов, которая строится программно.
Я хочу программно строить эту строку символов ОДИН раз в сеансе. Поэтому я и использовал код начальной инициализации в теле пакета, который будет выполняться один раз, при первом использовании пакета в сеансе.
Можно ли объяснить этот код подробнее? 25 июня 2002 года
Если просто вызывается функция, выполняется ли этот цикл?
В какой последовательности все будет выполняться?
Что, если строка большая, varchar2(4000), и строк около 2 миллионов?
(У меня есть поле комментария с такими интересными символами. Я хочу либо удалить их, либо заменить пробелами, но при этом тип данных - varchar2(4000)!!)
Можно ли в этом случае сделать то же самое?
Есть ли обратная функция для ascii()?
Ответ дяди Тома
Этот цикл выполняется при первом использовании пакета в сеансе. Так что, да, при первом вызове функции пакета этот цикл выполняется. Ни один из последующих вызовов "циклиться" не будет.
Выполняться все будет так, что этот код автоматически и "чудом" вызывается машиной plsql ПЕРЕД первым использованием любого компонента пакета. Как конструктор в Java или C++. Это - "чудо"
Да, это можно проделать с 2 миллионами строк типа varchar2(4000). Надо только иметь терпение.
Ну, и в чем проблема с этими символами? Если вы хотите их сохранить, измените соответственно код.
chr - обратная функция для ascii
Оригинал обсуждения этого вопроса можно найти здесь. Некоторые несущественные детали и комментарии читателей не переведены.
Раз уж мы затронули тему пакетов, немного синтаксиса - спецификация и тело пакета в Oracle 8.1.7 (в том числе, раздел инициализации)
Спецификация пакета
Тело пакета
Про то, как дядя Том получает такие красивые приглашения в командной строке SQL*Plus. Следите за новостями на сайте проекта Open Oracle.
Читайте также: