Oracle убрать символы из строки
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 "
4- LOWER
- Строка (string) конвертируется в письменные буквы.
Метод COUNT
Метод COUNT возвращает количество элементов в ассоциативном массиве, вложенной таблице или массиве VARRAY . В значении не учитываются элементы, удаленные из коллекции методом DELETE или TRIM .
Синтаксис вызова:
Рассмотрим пример. Прежде чем что-либо делать с коллекцией, мы проверяем, содержит ли она хотя бы один элемент:
Граничные условия
Для инициализированной коллекции, не содержащей ни одного элемента, COUNT возвращает нуль. Это же значение возвращается при вызове COUNT для пустого ассоциативного массива.
Возможные исключения
При вызове метода COUNT для неинициализированной вложенной таблицы или VARRAY инициируется заранее определенное исключение COLLECTION_IS_NULL . Такое исключение не может возникнуть при работе с ассоциативными массивами, не требующими инициализации.
Метод EXTEND
Чтобы добавить элемент во вложенную таблицу или массив VARRAY , необходимо сначала выделить для него область памяти. Соответствующая операция, не зависящая от присваивания элементу значения, выполняется методом EXTEND . Следует помнить, что указанный метод неприменим к ассоциативным массивам.
Метод EXTEND , как уже было сказано, добавляет элементы в коллекцию. При вызове без аргументов он добавляет один элемент со значением NULL . Вызов EXTEND ( n ) присоединяет n элементов со значением NULL , а вызов EXTEND ( n,i ) — n элементов, и всем им присваивает значение i -го элемента. Последняя форма метода применяется к коллекциям, для элементов которых задано ограничение NOT NULL .
Синтаксис перегруженного метода EXTEND :
В следующем примере процедура push добавляет в список один элемент и присваивает ему новое значение:
В другом фрагменте кода метод EXTEND используется для включения в коллекцию 10 новых элементов с одинаковыми значениями. Сначала в коллекцию добавляется один элемент, которому явно присваивается нужное значение. При повторном вызове метода EXTEND в коллекцию добавляется еще 9 элементов, которым присваивается значение первого элемента коллекции new_value :
Граничные условия
Если параметр n имеет значение NULL , метод не выполнит никаких действий.
Возможные исключения
При вызове метода EXTEND для неинициализированной вложенной таблицы или VARRAY инициируется исключение COLLECTION_IS_NULL . Попытка добавить в массив VARRAY элементы, индекс которых превышает максимальный индекс массива в его объявлении, инициирует исключение SUBSCRIPT_BEYOND_LIMIT .
Метод TRIM
Метод TRIM удаляет n последних элементов коллекции — вложенной таблицы или массива VARRAY . Если метод вызывается без аргументов, он удалит только один элемент. Как упоминалось ранее, при совместном использовании методов TRIM и DELETE возможна накладка: если заданный в вызове метода TRIM элемент был уже удален методом DELETE , метод TRIM «повторит» удаление, но считает его частью n, поэтому количество реально удаленных элементов окажется меньшим, чем вы рассчитывали.
Попытка вызова метода TRIM для ассоциативного массива приведет к ошибке компиляции.
Синтаксис метода TRIM :
Следующая функция извлекает из списка последнее значение и возвращает его вызывающему блоку. Операция извлечения реализуется как выборка значения с последующим усечением коллекции на один элемент:
Граничные условия
Если значение n равно NULL , метод не выполнит никаких действий.
Возможные исключения
При попытке удалить больше элементов, чем имеется в коллекции, инициируется исключение SUBSCRIPT_BEYOND_COUNT . Если метод TRIM вызывается для неинициализированной вложенной таблицы или массива VARRAY , инициируется исключение COLLECTION_IS_NULL .
Вызывая методы TRIM и DELETE для одной и той же коллекции, можно получить неожиданные результаты. На сколько элементов станет меньше в коллекции, если удалить последний элемент методом DELETE , а затем вызвать метод TRIM с тем же значением параметра? Казалось бы, это приведет к удалению двух элементов, но в действительности оба метода удалят один и тот же элемент. Чтобы избежать накладок, компания Oracle рекомендует использовать только один из этих двух методов при работе с конкретной коллекцией.
Следуйте за нами на нашей фан-странице, чтобы получать уведомления каждый раз, когда появляются новые статьи. Facebook
2- COALESCE
Функция COALESCE возвращает первое ненулевое выражение в списке. Если все выражения в списке оценок являются NULL, то функция COALESCE возвращает NULL.
- p_expression1, p_expression2, .. p_expressionN
- Выражения чтобы проверить non-null значения.
Методы PRIOR и NEXT
Методы PRIOR и NEXT используются для перемещения по коллекциям — вложенным таблицам, ассоциативным массивам и массивам VARRAY . Метод PRIOR возвращает индекс предыдущего, а метод NEXT — следующего элемента коллекции. Следующая функция возвращает сумму чисел, хранящихся в коллекции list_t :
Та же программа, но с перебором элементов от последней к первой определенной записи коллекции:
В данном случае направление перемещения не имеет значения, но в других программах оно может повлиять на результат обработки.
Граничные условия
Методы PRIOR и NEXT для инициализированной коллекции, не содержащей ни одного элемента, возвращают NULL . Если значение i больше или равно COUNT , метод NEXT возвращает NULL ; если i меньше или равно FIRST , метод PRIOR возвращает NULL .
Вплоть до версии Oracle12c, если коллекция не пуста, а параметр i больше или равен COUNT , метод PRIOR возвращает LAST ; если параметр i меньше FIRST , метод NEXT возвращает FIRST . Однако сохранение такого поведения в будущих версиях Oracle не гарантировано.
Возможные исключения
Вызов методов PRIOR и NEXT для неинициализированной вложенной таблицы или массива VARRAY генерирует исключение COLLECTION_IS_NULL .
Метод EXISTS
Метод EXISTS используется с вложенными таблицами, ассоциативными массивами и массивами VARRAY для определения наличия в коллекции заданного элемента. Если таковой имеется, метод возвращает значение TRUE , а если отсутствует — значение FALSE . Значение NULL не возвращается ни при каких условиях. Кроме того, EXISTS возвращает FALSE и в том случае, если заданный элемент был удален из коллекции с помощью метода TRIM или DELETE .
Следующий блок проверяет, присутствует ли заданный элемент в коллекции, и при положительном ответе присваивает ему значение NULL :
Граничные условия
Возможные исключения
Метод EXISTS не инициирует исключения.
Краткая сводка строковых функций
Как упоминалось ранее, PL/SQL предоставляет в распоряжение программиста широкий набор мощных, высокоуровневых строковых функций для получения информации о строках и модификации их содержимого. Следующий список дает представление об их возможностях и синтаксисе. За полной информацией о конкретных функциях обращайтесь к справочнику Oracle SQL Reference.
- ASCII(символ ) Возвращает числовой код (NUMBER) представления заданного символа в наборе символов базы данных.
- ASCIISTR(строка1) Получает строку в любом наборе символов и преобразует ее в строку ASCII-символов. Все символы, отсутствующие в кодировке ASCII, представляются в форме \XXXX, где XXXX — код символа в Юникоде.
- CHR(код)
Возвращает символ типа VARCHAR2 (длина 1), соответствующий заданному коду. Функция является обратной по отношению к функции ASCII. У нее имеется разновидность, удобная при работе с данными в национальных наборах символов:
Возвращает символ типа NVARCHAR2 из национального набора символов.
- COMPOSE(строка1)
Получает строку символов в формате Юникода и возвращает ее в нормализованном виде. Например, ненормализованное представление 'a\0303' определяет символ ' a ' с тильдой cверху (то есть a). Вызов COMPOSE('a\0303') возвращает значение ' \00E3' — шестнадцатеричный код символа a в Юникоде.
В Oracle9i Release 1 функция COMPOSE могла вызываться только из SQL-команд; в программах PL/SQL она использоваться не могла. Начиная с Oracle9i Release2, функция COMPOSE также может использоваться в выражениях PL/SQL.
- CONCAT(строка1, строка2)
Присоединяет строку2 в конец строки1. Аналогичного результата можно добиться при помощи выражения строка1 || строка2. Оператор || намного удобнее, поэтому функция CONCAT используется относительно редко. - CONVERT(строка1, набор_символов)
Преобразует строку из набора символов базы данных в заданный набор символов. При вызове также можно задать исходный набор символов:
CONVERT(строка1, конечный_набор, исходный_набор)
- DECOMPOSE(строка1)
Получает строку в Юникоде и возвращает строку, в которой все составные символы разложены на элементы. Функция является обратной по отношению к COMPOSE . Например, вызов DECOMPOSE ('a') возвращает строку ' a ~' (см. описание COMPOSE ).
Существует две разновидности этой функции:
- DECOMPOSE(строка1 CANONICAL)
Выполняет каноническую декомпозицию; полученный результат может быть восстановлен вызовом COMPOSE . Используется по умолчанию. - DECOMPOSE(строка1)
Декомпозиция выполняется в так называемом режиме совместимости. Восстановление вызовом COMPOSE может оказаться невозможным.
Функция DECOMPOSE , как и COMPOSE , не может напрямую вызываться в выражениях PL/SQL в Oracle9i Release 1; ее необходимо вызывать из инструкций SQL. Начиная с Oracle9i Release 2, это ограничение было снято.
Существует несколько разновидностей этой функции:
- INSTR(строка1, строка2, начальная_позиция)
Поиск строки2 в строке1 начинается с позиции, заданной последним параметром. По умолчанию поиск начинается с позиции 1, так что вызов INSTR(string1, string2, 1 ) эквивалентен вызову INSTR(string1, string2) . - INSTR(строка1, строка2, отрицательная_начальная_позиция)
Смещение начальной позиции задается не от начала, а от конца строки1. - INSTR(строка1, строка2, начальная_позиция, n )
Находит n-е вхождение строки2, начиная с заданной начальной позиции. - INSTR(строка1, строка2, отрицательная_начальная_позиция, n)
Находит n-е вхождение строки2, начиная с заданной начальной позиции от конца строки1.
Функция INSTR рассматривает строку как последовательность символов. Ее разновидности INSTRB, INSTR2 и INSTR4 рассматривают строку как последовательность байтов, кодовых единиц (code units) или кодовых индексов (code points) Юникода соответственно. Разновидность INSTRC рассматривает строку как последовательность полных символов Юникода. Например, строка 'a\0303' , которая представляет собой разложенный эквивалент '\00E3', или a , рассматривается как один символ. Напротив, функция INSTR рассматривает 'a\0303 ' как последовательность из двух символов.
- LEAST(строка1, строка2, . )
Получает одну или несколько строк и возвращает строку, которая оказалась бы первой (то есть наименьшей) при сортировке входных строк по возрастанию. Также см. описание функции GREATEST , обратной по отношению к LEAST . - LENGTH(строка1)
Возвращает количество символов в строке. Разновидности LENGTHB, LENGTH2 и LENGTH4 возвращают количество байтов, кодовых единиц (code units) или кодовых индексов (code points) Юникода соответственно. Разновидность LENGTHC возвращает количество полных символов Юникода, нормализованных по мере возможности (то есть с преобразованием 'a\0303 ' в '\00E3' ).
Функция LENGTH обычно не возвращает нуль. Вспомните, что Oracle рассматривает пустую строку ('') как NULL , поэтому вызов LENGTH ('') фактически эквивалентен попытке получить длину NULL ; ее результат тоже будет равен NULL . Единственное исключение встречается при применении LENGTH к типу CLOB . Тип CLOB может содержать 0 байт и при этом быть отличным от NULL . В этом единственном случае LENGTH возвращает 0.
- LOWER(строка1)
Преобразует все буквы заданной строки в нижний регистр. Функция является обратной по отношению к UPPER . Тип возвращаемого значения соответствует типу входных данных ( CHAR ,VARCHAR2, CLOB ). Также см. NLS_LOWER . - LPAD(строка1, итоговая_длина)
Возвращает значение строки1, дополненное слева пробелами до итоговой_длины . У функции существует следующая разновидность: - LPAD(строка1, итоговая_длина, заполнитель)
Присоединяет достаточное количество полных или частичных вхождений заполнителя, чтобы общая длина строки достигла заданной итоговой_длины . Например, вызов LPAD ( 'Merry Christmas!', 25, 'Ho! ') вернет результат ' Ho! Ho! HMerry Christmas!'.
- ?LTRIM(строка1)
Удаляет пробелы с левого края строки1. Также см. описания функций TRIM (стандарт ISO) и RTRIM . У функции существует следующая разновидность: - LTRIM(строка1, удаляемый_набор)
Удаляет любые символы, входящие в строку удаляемый_набор , от левого края строки1. - NCHR(код)
Возвращает символ типа NVARCHAR2 (длина 1) , соответствующий заданному коду. Функция CHR с условием USING NCHAR_CS реализует ту же функциональность, что и NCHR . - NLS_INITCAP (строка1)
Возвращает версию строки1, которая должна относиться к типу NVARCHAR2 или NCHAR , в которой первая буква каждого слова переводится в верхний регистр, а остальные буквы — в нижний. Функция возвращает значение типа VARCHAR2 . «Словом» считается последовательность символов, отделенная от остальных символов пробелом или символом, не являющимся буквенно-цифровым.
Вы можете задать порядок сортировки, влияющий на определение «первой буквы»:
- NLS_INITCAP(строка1, 'NLS_SORT=правило_сортировки')
В этой форме синтаксиса правило_сортировки представляет собой одно из допустимых названий правил сортировки, перечисленных в руководстве Oracle Database Globalization Support Guide, Appendix A, раздел «Linguistic Sorts».
Следующий пример показывает, чем функция INITCAP отличается от NLS_INITCAP :
В нидерландском языке последовательность символов « ? » рассматривается как один символ. Функция NLS_INITCAP распознает это обстоятельство при задании правила NLS_SORT и правильно преобразует символы слова « ?zer » («железо» по-нидерландски).
- NLS_LOWER(строка1) и NLS_LOWER(строка1, 'NLS_SORT=правило_сортировки ') Возвращает строку1, преобразованную в нижний регистр по правилам заданного языка. О том, как NLS_SORT может повлиять на результат преобразования, рассказано в описании функции NLS_INITCAP .
- NLS_UPPER(строка1) и NLS_UPPER(строка1, 'NLS_SORT=правило_сортировки') Возвращает строку1, преобразованную в верхний регистр по правилам заданного языка. О том, как NLS_SORT может повлиять на результат преобразования, рассказано в описании функции NLS_INITCAP .
- NLSSORT(строка1) и NLSSORT(строка1, 'NLS_SORT=правило_сортировки ') Возвращает строку байтов, которая может использоваться для сортировки строкового значения по правилам заданного языка. Строка возвращается в формате RAW . Например, сравнение двух строк по правилам французского языка выполняется так: IF NLSSORT(x, 'NLS_SORT=XFRENCH') > NLSSORT(y, 'NLS_SORT=XFRENCH') THEN. Если второй параметр не указан, функция использует порядок сортировки по умолчанию, назначенный для сеанса. Полный список правил приведен в руководстве Oracle Database Globalization Support Guide, Appendix A, раздел «Linguistic Sorts».
- REGEXP_COUNT, REGEXP_INSTR, REGEXP_LIKE, REGEXP_REPLACE, REGEXP_SUBSTR За описаниями этих функций, предназначенных для работы с регулярными выражениями, можно изучить эту статью.
- REPLACE(строка1, искомая_строка, замена) Возвращает строку, полученную в результате замены всех вхождений искомой_строки в строке1 строкой замена. Функция REPLACE может использоваться для замены всех вхождений определенной подстроки в одной инструкции.
- REPLACE(строка1, искомая_строка)
Возвращает строку, полученную в результате удаления всех вхождений искомой_строки из строки1. - RPAD(строка1, итоговая_длина)
Возвращает значение строки1, дополненное справа пробелами до итоговой_длины . У функции существует следующая разновидность: - RPAD(строка1, итоговая_длина, заполнитель)
Присоединяет достаточное количество полных или частичных вхождений заполнителя, чтобы общая длина строки достигла заданной итоговой_длины . Вызов RPAD('Merry Christmas!', 25, 'Ho! ') вернет результат 'Merry Christmas! Ho! Ho!'.
Функция RPAD дополняет строку справа, а парная ей функция LPAD — слева.
- RTRIM(строка1)
Удаляет пробелы с правого края строки1. Также см. описания функций TRIM (стандарт ISO) и LTRIM . У функции существует следующая разновидность: - RTRIM(строка1, удаляемый_набор)
Удаляет любые символы, входящие в строку удаляемый_набор , с правого края строки1. - SOUNDEX(строка1)
Возвращает строку с «фонетическим представлением» аргумента.
Пример:
При использовании функции SOUNDEX следует помнить несколько правил:
- Значение SOUNDEX всегда начинается с первой буквы входной строки.
- Возвращаемое значение генерируется только по первым пяти согласным в строке.
- Для вычисления цифровой части SOUNDEX используются только согласные. Все гласные в строке, кроме начальных, игнорируются.
- Функция SOUNDEX игнорирует регистр символов; для букв верхнего и нижнего регистра генерируются одинаковые значения SOUNDEX .
Функция SOUNDEX полезна для запросов, при которых точное написание значения в базе данных неизвестно или не может быть легко определенно.
Алгоритм SOUNDEX ориентирован на английский язык; в других языках он может работать плохо (или не работать вообще).
- SUBSTR(строка1, начальная_позиция, длина)
Возвращает подстроку из строки1, которая начинается с начальной_позиции и имеет заданную длину. Если количество символов до конца строки1 окажется меньше длины, возвращаются все символы от начальной позиции до конца строки. У функции существуют следующие разновидности: - SUBSTR(строка1, начальная_позиция)
Возвращает все символы от начальной_позиции до конца строки1. - SUBSTR(строка1, отрицательная_начальная_позиция, длина)
Начальная позиция подстроки отсчитывается от конца строки1. - SUBSTR(строка1, отрицательная_начальная_позиция)
Возвращает последние ABS( отрицательная_начальная_позиция ) строки.
Функция SUBSTR рассматривает строку как последовательность символов. Ее разновидности SUBSTRB, SUBSTR2 и SUBSTR4 рассматривают строку как последовательность байтов, кодовых единиц (code units) или кодовых индексов (code points) Юникода соответственно. Разновидность SUBSTRC рассматривает строку как последовательность полных символов Юникода. Например, строка 'a\0303' , которая представляет собой разложенный эквивалент '\00E3' , или a , рассматривается как один символ. Напротив, функция SUBSTR рассматривает 'a\0303' как последовательность из двух символов.
- TO_CHAR(национальные_символьные_данные)
Преобразует данные из национального набора символов в эквивалентное представление в наборе символов базы данных. Также см. TO_NCHAR .
Функция TO_CHAR также может использоваться для преобразования даты/ времени и чисел в удобочитаемую форму.
- TO_MULTI_BYTE(строка1)
Преобразует однобайтовые символы в их многобайтовые эквиваленты. В некоторых многобайтовых кодировках, и прежде всего UTF-8, может существовать несколько вариантов представления одного символа. Скажем, в UTF-8 представление буквы 'G' может содержать от 1 до 4 байт. Для перехода от однобайтового представления к многобайтовому используется функция TO_MULTI_BYTE . Данная функция является обратной по отношению к TO_SINGLE_BYTE . - TO_NCHAR(символы_в_наборе_базы_данных)
Преобразует данные из набора символов базы данных в эквивалентное представление в национальном наборе символов. Также см. TO_CHAR и TRANSLATE. USING.
Функция TO_NCHAR также может использоваться для преобразования даты/времени и чисел в удобочитаемую форму.
- TO_SINGLE_BYTE(строка1)
Преобразует многобайтовые символы в их однобайтовые эквиваленты. Функция является обратной по отношению к TO_MULTI_BYTE . - TRANSLATE(строка1, искомый_набор, набор_замены)
Заменяет в строке1 каждое вхождение символа из искомого_набора соответствующим символом набора_замены . Пример:
Если искомый_набор содержит больше символов, чем набор_замены , «лишние» символы, не имеющие соответствия в наборе_замены , не включаются в результат. Пример:
Буква « d » удалена, потому что она присутствует в искомом_наборе , но не имеет эквивалента в наборе_замены . Функция TRANSLATE заменяет отдельные символы, а функция REPLACE — целые строки.
- TRANSLATE(текст USING CHAR_CS) и TRANSLATE(текст USING NCHAR_CS)
Преобразует символьные данные в набор символов базы данных ( CHAR_CS ) или в национальный набор символов ( NCHAR_CS ). Выходным типом данных будет VARCHAR2 или NVARCHAR2 в зависимости от того, выполняется ли преобразование к набору символов базы данных или национальному набору символов соответственно.
Функция TRANSLATE. USING входит в число функций SQL по стандарту ISO. Начиная с Oracle9i Release 1, можно просто присвоить значение VARCHAR2 переменной типа NVARCHAR2 , и наоборот — система неявно выполнит нужное преобразование. Если же вы хотите выполнить преобразование явно, используйте функции TO_CHAR и TO_NCHAR для преобразования текста в набор символов базы данных и национальный набор символов соответственно. Oracle рекомендует пользоваться указанными функциями вместо TRANSLATE. USING , потому что они поддерживают более широкий набор входных типов данных.
- TRIM(FROM строка1)
Возвращает строку, полученную в результате удаления из строки1 всех начальных и конечных пробелов. У функции существуют следующие разновидности: - TRIM(LEADING FROM . )
Удаление только начальных пробелов. - TRIM(TRAILING FROM . )
Удаление только конечных пробелов. - TRIM(BOTH FROM . )
Удаление как начальных, так и конечных пробелов (используется по умолчанию). - TRIM (. удаляемый_символ FROM строка1)
Удаление вхождений одного удаляемого_символа на выбор программиста.
Функция TRIM была включена в Oracle8i для обеспечения более полной совместимости со стандартом ISO SQL. Она сочетает в себе функциональность LTRIM и RTRIM , но отличается от них тем, что TRIM позволяет задать только один удаляемый символ, тогда как при использовании LTRIM и RTRIM можно задать набор удаляемых символов.
- UNISTR(строка1)
Возвращает строку1, преобразованную в Юникод; таким образом, функция является обратной по отношению к ASCIISTR . Для представления непечатаемых символов во входной строке можно использовать запись \XXXX, где XXXX — кодовый индекс символа в Юникоде. Пример:
Функция предоставляет удобный доступ ко всему набору символов Юникода, в том числе и к тем, которые не могут вводиться непосредственно с клавиатуры.
PL/SQL предоставляет для создаваемых вами коллекций множество встроенных функций и процедур, называемых методами коллекций. Эти методы предназначены для получения информации о содержимом коллекции и ее изменения. Их полный список приведен в табл. 1.
Метод (функция или процедура) | Описание |
COUNT (функция) | Возвращает текущее значение элементов в коллекции |
DELETE (процедура) | Удаляет из коллекции один или несколько элементов. Уменьшает значение, возвращаемое функцией COUNT, если заданные элементы еще не удалены. Со структурами VARRAY может использоваться только для удаления всего содержимого |
EXISTS (функция) | Возвращает значение TRUE или FALSE , определяющее, существует ли в коллекции заданный элемент |
EXTEND (процедура) | Увеличивает количество элементов во вложенной таблице или VARRAY , а также значение, возвращаемое функцией COUNT |
FIRST, LAST (функции) | Возвращают индексы первого ( FIRST ) и последнего ( LAST ) элемента в коллекции |
LIMIT (функция) | Возвращает максимальное количество элементов в массиве VARRAY |
PRIOR, NEXT (функции) | Возвращают индексы элементов, предшествующих заданному ( PRIOR ) и следующему за ним ( NEXT ). Всегда используйте PRIOR и NEXT для перебора коллекций, особенно при работе с разреженными (или потенциально разреженными) коллекциями |
TRIM (функция) | Удаляет элементы, начиная с конца коллекции (элемент с наибольшим индексом) |
Все эти конструкции называются методами, потому что синтаксис их вызова отличается от синтаксиса вызова обычных процедур и функций. Это типичный синтаксис вызова методов, используемый в объектно-ориентированных языках программирования — таких, как Java.
Рассмотрим синтаксис вызова методов на примере LAST . Эта функция возвращает наибольший индекс элемента ассоциативного массива. Стандартный вызов этой функции выглядел бы так:
Иначе говоря, ассоциативный массив передается ей в качестве аргумента. Но поскольку функция LAST является методом, она «принадлежит» объекту — в данном случае ассоциативному массиву. Правильный синтаксис ее вызова выглядит так:
В общем случае синтаксис вызова методов ассоциативного массива выглядит так:
- Операция, не требующая передачи аргументов:
- Операция, аргументами которой являются индексы элементов:
Например, следующая команда возвращает TRUE , если в ассоциативном массиве company_tab определена запись 15:
Методы коллекций недоступны из SQL; их можно использовать только в программах PL/SQL.
Методы FIRST и LAST
Методы FIRST и LAST возвращают соответственно наименьший и наибольший индексы элементов вложенной таблицы, ассоциативного массива или массива VARRAY . Для ассоциативных массивов, индексируемых строками, эти методы возвращают строки; «наименьшее» и «наибольшее» значения определяются порядком набора символов, используемого данным сеансом. Для других типов коллекций методы возвращают целые числа.
Синтаксис этих функций:
Так, следующий фрагмент перебирает все элементы коллекции от начала к концу:
Запомните, что такой цикл будет выполнен корректно (то есть не породит исключения NO_DATA_FOUND ) лишь при условии, что коллекция является плотной.
В следующем примере для добавления элементов в конец ассоциативного массива используется оператор COUNT . Цикл FOR с курсором используется для копирования данных из базы в ассоциативный массив. При выборке первой записи коллекция companies пуста, поэтому COUNT возвращает 0.
Граничные условия
Если методы FIRST и LAST вызываются для инициализированных коллекций, не содержащих ни одного элемента, они возвращают NULL . Для массива VARRAY , всегда содержащего хотя бы один элемент, FIRST всегда возвращает 1, а LAST — то же значение, что и метод COUNT .
Возможные исключения
При вызове методов FIRST и LAST для неинициализированной вложенной таблицы или массива VARRAY инициируется исключение COLLECTION_ IS_NULL.
3- UPPER
- Строка (string) конвертируется в заглавные буквы.
5- INITCAP
Функция INITCAP возвращает строку символов с первой буквой каждого слова, конвертировенной в заглавную букву (Еще называется "title case"). Все другие буквы в строке будут конвертированы в письменные буквы. Слова отделены пробелами (white space) (spaces, tabs, carriage returns, formfeeds, vertical-tabs, newlines) или символами, которые не являются буквами или числами.
- Строка (string) параметров, у которых все первые сиволы слова будут конвертированы в заглавные буквы, а остальные буквы конвертируются в письменные.
Метод DELETE
Метод DELETE предназначен для удаления одного, нескольких или всех элементов ассоциативного массива, вложенной таблицы или массива VARRAY . При вызове без аргументов он удаляет все элементы коллекции. Вызов DELETE (i) удаляет i-й элемент вложенной таблицы или ассоциативного массива. А вызов DELETE ( i,j ) удаляет все элементы с индексами от i до j включительно. Если коллекция представляет собой ассоциативный массив, индексируемый строками, i и j должны быть строковыми значениями; в противном случае они являются целыми числами.
При вызове с аргументами метод резервирует место, занимавшееся «удаленным» элементом, и позднее этому элементу можно присвоить новое значение.
Фактически PL/SQL освобождает память лишь при условии, что программа удаляет количество элементов, достаточное для освобождения целой страницы памяти. (Если же метод DELETE вызывается без параметров и очищает всю коллекцию, память освобождается немедленно.)
Применительно к массивам VARRAY метод DELETE может вызываться только без аргументов. Иначе говоря, с помощью указанного метода из этой структуры нельзя удалять отдельные элементы, поскольку в таком случае она станет разреженной, что недопустимо. Единственный способ удалить из VARRAY один или несколько элементов — воспользоваться методом TRIM , предназначенным для удаления группы расположенных рядом элементов, начиная с конца коллекции.
Следующая процедура удаляет из коллекции все элементы, кроме последнего. В ней используются четыре метода: FIRST — для получения номера первого удаляемого элемента; LAST — для получения номера последнего удаляемого элемента; PRIOR — для определения номера предпоследнего элемента; DELETE — для удаления всех элементов, кроме последнего:
Несколько дополнительных примеров:
- Удаление всех строк из таблицы names :
- Удаление 77-й строки из таблицы globals :
- Удаление из таблицы temp_reading всех элементов, начиная с индекса –15 000 и до индекса 0 включительно:
Граничные условия
Если значения индексов i и/или j указывают на несуществующие элементы, DELETE пытается «сделать наилучшее» и не генерирует исключение. Например, если таблица содержит три элемента с индексами 1, 2 и 3, то вызов метода DELETE (–5,1) удалит только один элемент с индексом 1, а вызов DELETE (–5) не изменит состояния коллекции.
Возможные исключения
Вызов метода DELETE для неинициализированной вложенной таблицы или массива VARRAY инициирует исключение COLLECTION_ IS_NULL .
1- ASCII
Функция ASCII является функцией в Oracle PL/SQL возвращает номерной код, который представляет символ.
- Символ передан для получения кода. Если передать больше на один символ, функция ASCII возвращает значение первого символа и пропускает остальные символы.
6- CHR
Функция CHR возвращает символ ASCII соответствующий переданному значению. Если так же определить NCHAR_CS, то она вернет символы, соответствующие кода определенной страны.
Метод LIMIT
Метод LIMIT возвращает максимальное количество элементов, которое можно определить в массиве VARRAY . В случае вложенной таблицы или ассоциативного массива он возвращает NULL . Синтаксис этого метода:
В следующем примере перед добавлением нового элемента в конец массива VARRAY мы сначала проверяем, есть ли в нем еще свободное место:
Граничные условия
У метода LIMIT граничных условий не существует.
Возможные исключения
Вызов метода LIMIT для неинициализированной вложенной таблицы или массива VARRAY генерирует исключение COLLECTION_ IS_NULL .
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.
Символьная функция получает в качестве параметра одно или несколько символьных значений и возвращает символьное и числовое значение. Если символьная функция возвращает символьное значение, оно всегда имеет тип VARCHAR2 (переменная длина) — кроме функций UPPER и LOWER . Эти функции преобразуют заданную строку к верхнему или нижнему регистру соответственно и возвращают значение фиксированной длины типа CHAR , если переданные в аргументах строки имели тип CHAR .
Читайте также: