Oracle найти символ в строке
В посте рассматриваются однострочные функции 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.
The INSTR functions search string for substring . The search operation is defined as comparing the substring argument with substrings of string of the same length for equality until a match is found or there are no more substrings left. Each consecutive compared substring of string begins one character to the right (for forward searches) or one character to the left (for backward searches) from the first character of the previous compared substring. If a substring that is equal to substring is found, then the function returns an integer indicating the position of the first character of this substring. If no such substring is found, then the function returns zero.
position is an nonzero integer indicating the character of string where Oracle Database begins the search—that is, the position of the first character of the first substring to compare with substring . If position is negative, then Oracle counts backward from the end of string and then searches backward from the resulting position.
occurrence is an integer indicating which occurrence of substring in string Oracle should search for. The value of occurrence must be positive. If occurrence is greater than 1, then the database does not return on the first match but continues comparing consecutive substrings of string , as described above, until match number occurrence has been found.
INSTR accepts and returns positions in characters as defined by the input character set, with the first character of string having position 1. INSTRB uses bytes instead of characters. INSTRC uses Unicode complete characters. INSTR2 uses UCS2 code points. INSTR4 uses UCS4 code points.
string can be any of the data types CHAR , VARCHAR2 , NCHAR , NVARCHAR2 , CLOB , or NCLOB . The exceptions are INSTRC , INSTR2 , and INSTR4 , which do not allow string to be a CLOB or NCLOB .
substring can be any of the data types CHAR , VARCHAR2 , NCHAR , NVARCHAR2 , CLOB , or NCLOB .
The value returned is of NUMBER data type.
Both position and occurrence must be of data type NUMBER , or any data type that can be implicitly converted to NUMBER , and must resolve to an integer. The default values of both position and occurrence are 1, meaning Oracle begins searching at the first character of string for the first occurrence of substring . The return value is relative to the beginning of string , regardless of the value of position .
Table 2-8 for more information on implicit conversion
Appendix C in Oracle Database Globalization Support Guide for the collation determination rules, which define the collation the INSTR functions use to compare the substring argument with substrings of string
The following example searches the string CORPORATE FLOOR , beginning with the third character, for the string " OR ". It returns the position in CORPORATE FLOOR at which the second occurrence of " OR " begins:
In the next example, Oracle counts backward from the last character to the third character from the end, which is the first O in FLOOR . Oracle then searches backward for the second occurrence of OR , and finds that this second occurrence begins with the second character in the search string :
The INSTR functions search string for substring . The function returns an integer indicating the position of the character in string that is the first character of this occurrence. INSTR calculates strings using characters as defined by the input character set. INSTRB uses bytes instead of characters. INSTRC uses Unicode complete characters. INSTR2 uses UCS2 code points. INSTR4 uses UCS4 code points.
position is an nonzero integer indicating the character of string where Oracle Database begins the search. If position is negative, then Oracle counts backward from the end of string and then searches backward from the resulting position.
occurrence is an integer indicating which occurrence of string Oracle should search for. The value of occurrence must be positive.
Both string and substring can be any of the datatypes CHAR , VARCHAR2 , NCHAR , NVARCHAR2 , CLOB , or NCLOB . The value returned is of NUMBER datatype.
Both position and occurrence must be of datatype NUMBER , or any datatype that can be implicitly converted to NUMBER , and must resolve to an integer. The default values of both position and occurrence are 1, meaning Oracle begins searching at the first character of string for the first occurrence of substring . The return value is relative to the beginning of string , regardless of the value of position , and is expressed in characters. If the search is unsuccessful (if substring does not appear occurrence times after the position character of string ), then the return value is 0.
Table 2-10, "Implicit Type Conversion Matrix" for more information on implicit conversion
The following example searches the string CORPORATE FLOOR , beginning with the third character, for the string " OR ". It returns the position in CORPORATE FLOOR at which the second occurrence of " OR " begins:
In the next example, Oracle counts backward from the last character to the third character from the end, which is the first O in FLOOR . Oracle then searches backward for the second occurrence of OR , and finds that this second occurrence begins with the second character in the search string :
Символьная функция получает в качестве параметра одно или несколько символьных значений и возвращает символьное и числовое значение. Если символьная функция возвращает символьное значение, оно всегда имеет тип VARCHAR2 (переменная длина) — кроме функций UPPER и LOWER . Эти функции преобразуют заданную строку к верхнему или нижнему регистру соответственно и возвращают значение фиксированной длины типа CHAR , если переданные в аргументах строки имели тип CHAR .
Подсчет совпадений
Еще одна типичная задача — подсчет количества совпадений регулярного выражения в строке. До выхода Oracle11g программисту приходилось в цикле перебирать и подсчитывать совпадения. Теперь для этого можно воспользоваться новой функцией REGEXP_COUNT . Общий синтаксис ее вызова:
Здесь исходная_строка — строка, в которой выполняется поиск; шаблон — регулярное выражение, совпадение которого ищется в исходной_строке; начальная_позиция — позиция, с которой начинается поиск; модификаторы — один или несколько модификаторов, управляющих процессом поиска.
Поиск совпадения
Функция REGEXP_INSTR используется для поиска совпадений шаблона в строке. Общий синтаксис REGEXP_INSTR :
Здесь исходная_строка — строка, в которой выполняется поиск; шаблон — регулярное выражение, совпадение которого ищется в исходной_строке; начальная_позиция — позиция, с которой начинается поиск; номер — порядковый номер совпадения (1 = первое, 2 = второе и т. д.); флаг_возвращаемого_значения — 0 для начальной позиции или 1 для конечной позиции совпадения; модификаторы — один или несколько модификаторов, управляющих процессом поиска (например, i для поиска без учета регистра). Начиная с Oracle11g, также можно задать параметр подвыражение (1 = первое, 2 = второе и т. д.), чтобы функция REGEXP_INST возвращала начальную позицию заданного подвыражения (части шаблона, заключенной в круглые скобки).
Например, чтобы найти первое вхождение имени, начинающегося с буквы A и завершающегося согласной буквой, можно использовать следующее выражение:
При выполнении этого фрагмента выясняется, что имя на букву A, завершающееся согласной (Andrew), начинается в позиции 22. А вот как проходило построение шаблона:
- A Совпадение начинается с буквы A. Беспокоиться о запятых не нужно — на этой стадии мы уже знаем, что работаем со списком, разделенным запятыми.
- A[a-z ]* За буквой A следует некоторое количество букв или пробелов. Квантификатор * указывает, что за буквой A следует ноль или более таких символов.
- A[a-z ]*[^aeiou] В выражение включается компонент [ ^aeiou ], чтобы имя могло заканчиваться любым символом, кроме гласной. Знак ^ инвертирует содержимое квадратных скобок —
совпадает любой символ, кроме гласной буквы. Так как квантификатор не указан, требуется присутствие ровно одного такого символа. - A[a-z ]*[^aeiou], Совпадение должно завершаться запятой; в противном случае шаблон найдет совпадение для подстроки «An» в имени «Anna». Хотя добавление запятой решает эту проблему, тут же возникает другая: шаблон не найдет совпадение для имени «Aaron» в конце строки.
- A[a-z ]*[^aeiou],|A[a-z ]*[^aeiou]$ В выражении появляется вертикальная черта (|), обозначение альтернативы: общее совпадение находится при совпадении любого из вариантов. Первый вариант завершается запятой, второй — нет. Второй вариант учитывает возможность того, что текущее имя стоит на последнем месте в списке, поэтому он привязывается к концу строки метасимволом $ .
Регулярные выражения — далеко не простая тема! Новички часто сталкиваются с нюансами обработки регулярных выражений, которые часто преподносят неприятные сюрпризы. Я потратил некоторое время на работу над этим примером и несколько раз зашел в тупик, прежде чем выйти на правильный путь. Не отчаивайтесь — с опытом писать регулярные выражения становится проще.
Функция REGEXP_INSTR приносит пользу в некоторых ситуациях, но обычно нас больше интересует текст совпадения, а не информация о его положении в строке.
Получение текста совпадения
Для демонстрации получения текста совпадения мы воспользуемся другим примером. Телефонные номера строятся по определенной схеме, но у этой схемы существует несколько разновидностей. Как правило, телефонный номер начинается с кода города (три цифры), за которым следует код станции (три цифры) и локальный номер (четыре цифры). Таким образом, телефонный номер представляет собой строку из 10 цифр. Однако существует много альтернативных способов представления числа. Код города может быть заключен в круглые скобки и обычно (хотя и не всегда) отделяется от остального номера пробелом, точкой или дефисом. Код станции обычно (но тоже не всегда) отделяется от локального номера пробелом, точкой или дефисом. Таким образом, любая из следующих форм записи телефонного номера является допустимой:
С подобными нежесткими схемами записи легко работать при помощи регулярных выражений, но очень трудно без них. Мы воспользуемся функцией REGEXP_SUBSTR для извлечения телефонного номера из строки с контактными данными:
Результат работы программы:
Ого! Шаблон получился довольно устрашающим. Давайте разобьем его на составляющие:
- \(? Шаблон начинается с необязательного символа открывающей круглой скобки. Так как круглые скобки в языке регулярных выражений являются метасимволами (то есть имеют специальное значение), для использования в качестве литерала символ круглой скобки необходимо экранировать ( escape ), поставив перед ним символ \ (обратная косая черта). Вопросительный знак — квантификатор, обозначающий ноль или одно вхождение предшествующего символа. Таким образом, эта часть выражения описывает необязательный символ открывающей круглой скобки.
- \d \d — один из операторов, появившихся в Oracle10g Release 2 под влиянием языка Perl. Он обозначает произвольную цифру. Квантификатор <> указывает, что предшествующий символ входит в шаблон заданное количество раз (в данном случае три). Эта часть шаблона описывает три цифры.
- \)? Необязательный символ закрывающей круглой скобки.
- [[:space:]\.\-]? В квадратных скобках перечисляются символы, для которых обнаруживается совпадение — в данном случае это пропуск, точка или дефис. Конструкция [ :space: ] обозначает символьный класс POSIX для пропускных символов (пробел, табуляция, новая строка) в текущем наборе NLS. Точка и дефис являются метасимволами, поэтому в шаблоне их необходимо экранировать обратной косой чертой. Наконец, ? означает ноль или одно вхождение предшествующего символа. Эта часть шаблона описывает необязательный пропуск, точку или дефис.
- \d Эта часть шаблона описывает три цифры (см. выше).
- [[:space:]\.\-]? Эта часть шаблона описывает необязательный пропуск, точку или дефис (см. выше).
- \d Четыре цифры (см. выше).
Обязательно комментируйте свой код, использующий регулярные выражения, — это пригодится тому, кто будет разбираться в нем (вполне возможно, это будете вы сами через полгода).
Общий синтаксис REGEXP_SUBSTR :
Функция REGEXP_SUBSTR возвращает часть исходной_строки, совпадающую с шаблоном или подвыражением. Если совпадение не обнаружено, функция возвращает NULL . Здесь исходная_строка — строка, в которой выполняется поиск; шаблон — регулярное выражение, совпадение которого ищется в исходной_строке ; начальная_позиция — позиция, с которой начинается поиск; номер — порядковый номер совпадения (1 = первое, 2 = второе и т. д.); модификаторы — один или несколько модификаторов, управляющих процессом поиска.
Начиная с Oracle11g, также можно задать параметр подвыражение (1 = первое, 2 = второе и т. д.), чтобы функция возвращала начальную позицию заданного подвыражения (части шаблона, заключенной в круглые скобки). Подвыражения удобны в тех случаях, когда требуется найти совпадение для всего шаблона, но получить совпадение только для его части. Скажем, если мы хотим найти телефонный номер и извлечь из него код города, мы заключаем часть шаблона, описывающую код города, в круглые скобки, превращая ее в подвыражение:
Проверка наличия совпадения
Регулярные выражения используются для описания текста, который требуется найти в строке (и возможно, подвергнуть дополнительной обработке). Давайте вернемся к примеру, который приводился ранее в этом блоге:
Допустим, мы хотим определить на программном уровне, содержит ли строка список имен, разделенных запятыми. Для этого мы воспользуемся функцией REGEXP_LIKE , обнаруживающей совпадения шаблона в строке:
Чтобы разобраться в происходящем, необходимо начать с выражения, описывающего искомый текст. Общий синтаксис функции REGEXP_LIKE выглядит так:
Здесь исходная_строка — символьная строка, в которой ищутся совпадения; шаблон — регулярное выражение, совпадения которого ищутся в исходной_строке; модификаторы — один или несколько модификаторов, управляющих процессом поиска. Если функция REGEXP_LIKE находит совпадение шаблона в исходной_строке , она возвращает логическое значение TRUE ; в противном случае возвращается FALSE .
Процесс построения регулярного выражения выглядел примерно так:
- [a-z A-Z] Каждый элемент списка имен может состоять только из букв и пробелов. Квадратные скобки определяют набор символов, которые могут входить в совпадение. Диапазон a–z описывает все буквы нижнего регистра, а диапазон A–Z — все буквы верхнего регистра. Пробел находится между двумя компонентами выражения. Таким образом, этот шаблон описывает один любой символ нижнего или верхнего регистра или пробел.
- [a-z A-Z] * Звездочка является квантификатором — служебным символом, который указывает, что каждый элемент списка содержит ноль или более повторений совпадения, описанного шаблоном в квадратных скобках.
- [a-z A-Z] *, Каждый элемент списка должен завершаться запятой. Последний элемент является исключением, но пока мы не будем обращать внимания на эту подробность.
- ([a-z A-Z] *,) Круглые скобки определяют подвыражение, которое описывает некоторое количество символов, завершаемых запятой. Мы определяем это подвыражение, потому что оно должно повторяться при поиске.
- ([a-z A-Z ]*,)+ Знак + — еще один квантификатор, применяемый к предшествующему элементу (то есть к подвыражению в круглых скобках). В отличие от * знак + означает «одно или более повторений». Список, разделенный запятыми, состоит из одного или нескольких повторений подвыражения.
- ( [a-z A-Z]*,)+([a-z A-Z]*) В шаблон добавляется еще одно подвыражение: ( [a-z A-Z] *). Оно почти совпадает с первым, но не содержит запятой. Последний элемент списка не завершается запятой.
- ([a-z A-Z]*,)+([a-z A-Z]*) Мы добавляем квантификатор , чтобы разрешить вхождение ровно одного элемента списка без завершающей запятой.
- ^ ([a-z A-Z]*,)+([a-z A-Z]*)$ Наконец, метасимволы ^ и $ привязывают потенциальное совпадение к началу и концу целевой строки. Это означает, что совпадением шаблона может быть только вся строка вместо некоторого подмножества ее символов.
Функция REGEXP_LIKE анализирует список имен и проверяет, соответствует ли он шаблону. Эта функция оптимизирована для простого обнаружения совпадения шаблона в строке, но другие функции способны на большее!
Краткая сводка строковых функций
Как упоминалось ранее, 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 — кодовый индекс символа в Юникоде. Пример:
Функция предоставляет удобный доступ ко всему набору символов Юникода, в том числе и к тем, которые не могут вводиться непосредственно с клавиатуры.
В Oracle10g в области работы со строками произошли очень серьезные изменения: была реализована поддержка регулярных выражений. Причем речь идет не об упрощенной поддержке регулярных выражений вроде предиката LIKE, которая встречается в других СУБД. Компания Oracle предоставила в наше распоряжение отлично проработанный, мощный набор функций — то, что было необходимо в PL/SQL.
Замена текста REGEXP_REPLACE
Поиск и замена — одна из лучших областей применения регулярных выражений. Текст замены может включать ссылки на части исходного выражения (называемые обратными ссылками), открывающие чрезвычайно мощные возможности при работе с текстом. Допустим, имеется список имен, разделенный запятыми, и его содержимое необходимо вывести по два имени в строке. Одно из решений заключается в том, чтобы заменить каждую вторую запятую символом новой строки. Сделать это при помощи стандартной функции REPLACE нелегко, но с функцией REGEXP_REPLACE задача решается просто. Общий синтаксис ее вызова:
Здесь исходная_строка — строка, в которой выполняется поиск; шаблон — регулярное выражение, совпадение которого ищется в исходной_строке; начальная_позиция — позиция, с которой начинается поиск; модификаторы — один или несколько модификаторов, управляющих процессом поиска. Пример:
Результат выглядит так:
При вызове функции REGEXP_REPLACE передаются три аргумента:
- names — исходная строка;
- '([a-z A-Z]*),([a-z A-Z]*),' — выражение, описывающее заменяемый текст (см. ниже);
- '\1,\2 ' || chr(10) — текст замены. \1 и \2 — обратные ссылки, заложенные в основу нашего решения. Подробные объяснения также приводятся ниже.
Выражение, описывающее искомый текст, состоит из двух подвыражений в круглых скобках и двух запятых.
- ([a-z A-Z]*) Совпадение должно начинаться с имени.
- , За именем должна следовать запятая.
- ([a-z A-Z]*) Затем идет другое имя.
- , И снова одна запятая.
Наша цель — заменить каждую вторую запятую символом новой строки. Вот почему выражение написано так, чтобы оно совпадало с двумя именами и двумя запятыми. Также запятые не напрасно выведены за пределы подвыражений.
Первое совпадение для нашего выражения, которое будет найдено при вызове REGEXP_REPLACE , выглядит так:
Два подвыражения соответствуют именам « Anna » и « Matt ». В основе нашего решения лежит возможность ссылаться на текст, совпавший с заданным подвыражением, через обратную ссылку. Обратные ссылки \1 и \2 в тексте замены ссылаются на текст, совпавший с первым и вторым подвыражением. Вот что происходит:
Вероятно, вы уже видите, какие мощные инструменты оказались в вашем распоряжении. Запятые из исходного текста попросту не используются. Мы берем текст, совпавший с двумя подвыражениями (имена «Anna» и «Matt»), и вставляем их в новую строку с одной запятой и одним символом новой строки.
Но и это еще не все! Текст замены легко изменить так, чтобы вместо запятой в нем использовался символ табуляции (ASCII-код 9):
Теперь результаты выводятся в два аккуратных столбца:
Поиск и замена с использованием регулярных выражений — замечательная штука. Это мощный и элегантный механизм, с помощью которого можно сделать очень многое.
Максимализм и минимализм
Концепции максимализма и минимализма играют важную роль при написании регулярных выражений. Допустим, из разделенного запятыми списка имен нужно извлечь только первое имя и следующую за ним запятую. Список, уже приводившийся ранее, выглядит так:
Казалось бы, нужно искать серию символов, завершающуюся запятой:
Давайте посмотрим, что из этого получится:
Результат выглядит так:
Совсем не то. Что произошло? Дело в «жадности» регулярных выражений: для каждого элемента регулярного выражения подыскивается максимальное совпадение, состоящее из как можно большего количества символов. Когда мы с вами видим конструкцию:
у нас появляется естественное желание остановиться у первой запятой и вернуть строку « Anna ,». Однако база данных пытается найти самую длинную серию символов, завершающуюся запятой; база данных останавливается не на первой запятой, а на последней.
В версии Oracle Database 10g Release 1, в которой впервые была представлена поддержка регулярных выражений, возможности решения проблем максимализма были весьма ограничены. Иногда проблему удавалось решить изменением формулировки регулярного выражения — например, для выделения первого имени с завершающей запятой можно использовать выражение [^,]*, . Однако в других ситуациях приходилось менять весь подход к решению, часто вплоть до применения совершенно других функций.
Начиная с Oracle Database 10g Release 2, проблема максимализма отчасти упростилась с введением минимальных квантификаторов (по образцу тех, которые поддерживаются в Perl ). Добавляя вопросительный знак к квантификатору после точки, то есть превращая * в *?, я ищу самую короткую последовательность символов перед запятой:
Теперь результат выглядит так, как и ожидалось:
Минимальные квантификаторы останавливаются на первом подходящем совпадении, не пытаясь захватить как можно больше символов.
Подробнее о регулярных выражениях
Регулярные выражения на первый взгляд просты, но эта область на удивление глубока и нетривиальна. Они достаточно просты, чтобы вы начали пользоваться ими после прочтения этой статьи (хочется надеяться!), и все же вам предстоит очень много узнать. Некоторые источники информации от компании Oracle и издательства O’Reilly :
Читайте также: