В Oracle10g в области работы со строками произошли очень серьезные изменения: была реализована поддержка регулярных выражений. Причем речь идет не об упрощенной поддержке регулярных выражений вроде предиката LIKE, которая встречается в других СУБД. Компания Oracle предоставила в наше распоряжение отлично проработанный, мощный набор функций — то, что было необходимо в PL/SQL.
Регулярные выражения образуют своего рода язык для описания и обработки текста. Читатели, знакомые с языком Perl, уже разбираются в этой теме, поскольку Perl способствовал распространению регулярных выражений в большей степени, чем любой другой язык. Поддержка регулярных выражений в Oracle10g довольно близко соответствовала стандарту регулярных выражений POSIX (Portable Operating System Interface). В Oracle10g Release 2 появилась поддержка многих нестандартных, но весьма полезных операторов из мира Perl, а в Oracle11g возможности регулярных выражений были дополнительно расширены.
Проверка наличия совпадения
Регулярные выражения используются для описания текста, который требуется найти в строке (и возможно, подвергнуть дополнительной обработке). Давайте вернемся к примеру, который приводился ранее в этом блоге:
DECLARE names VARCHAR2(60) := 'Anna,Matt,Joe,Nathan,Andrew,Aaron,Jeff';
Допустим, мы хотим определить на программном уровне, содержит ли строка список имен, разделенных запятыми. Для этого мы воспользуемся функцией REGEXP_LIKE, обнаруживающей совпадения шаблона в строке:
DECLARE
names VARCHAR2(60) := 'Anna,Matt,Joe,Nathan,Andrew,Jeff,Aaron';
names_adjusted VARCHAR2(61);
comma_delimited BOOLEAN;
BEGIN
--Поиск по шаблону
comma_delimited := REGEXP_LIKE(names,'^([a-z A-Z]*,)+([a-z A-Z]*){1}$');
--Вывод результата
DBMS_OUTPUT.PUT_LINE(
CASE comma_delimited
WHEN true THEN 'Обнаружен список с разделителями!'
ELSE 'Совпадение отсутствует.'
END);
END;
Результат:
Обнаружен список с разделителями
Чтобы разобраться в происходящем, необходимо начать с выражения, описывающего искомый текст. Общий синтаксис функции 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]*){1}Мы добавляем квантификатор {1}, чтобы разрешить вхождение ровно одного элемента списка без завершающей запятой.- ^
([a-z A-Z]*,)+([a-z A-Z]*){1}$Наконец, метасимволы ^ и$привязывают потенциальное совпадение к началу и концу целевой строки. Это означает, что совпадением шаблона может быть только вся строка вместо некоторого подмножества ее символов.
Функция REGEXP_LIKE анализирует список имен и проверяет, соответствует ли он шаблону. Эта функция оптимизирована для простого обнаружения совпадения шаблона в строке, но другие функции способны на большее!
Поиск совпадения
Функция REGEXP_INSTR используется для поиска совпадений шаблона в строке. Общий синтаксис REGEXP_INSTR:
REGEXP_INSTR (исходная_строка, шаблон [,начальная_позиция [,номер [,флаг_возвращаемого_ значения [,модификаторы [,подвыражение]]]]])
Здесь исходная_строка — строка, в которой выполняется поиск; шаблон — регулярное выражение, совпадение которого ищется в исходной_строке; начальная_позиция — позиция, с которой начинается поиск; номер — порядковый номер совпадения (1 = первое, 2 = второе и т. д.); флаг_возвращаемого_значения — 0 для начальной позиции или 1 для конечной позиции совпадения; модификаторы — один или несколько модификаторов, управляющих процессом поиска (например, i для поиска без учета регистра). Начиная с Oracle11g, также можно задать параметр подвыражение (1 = первое, 2 = второе и т. д.), чтобы функция REGEXP_INST возвращала начальную позицию заданного подвыражения (части шаблона, заключенной в круглые скобки).
Например, чтобы найти первое вхождение имени, начинающегося с буквы A и завершающегося согласной буквой, можно использовать следующее выражение:
DECLARE names VARCHAR2(60) := 'Anna,Matt,Joe,Nathan,Andrew,Jeff,Aaron'; names_adjusted VARCHAR2(61); comma_delimited BOOLEAN; j_location NUMBER; BEGIN -- Поиск по шаблону comma_delimited := REGEXP_LIKE(names,'^([a-z ]*,)+([a-z ]*)$', 'i'); -- Продолжить, только если действительно был обнаружен список, -- разделенный запятыми. IF comma_delimited THEN j_location := REGEXP_INSTR(names, 'A[a-z]*[^aeiou],|A[a-z]*[^aeiou]$'); DBMS_OUTPUT.PUT_LINE( J_location); END IF; END;
При выполнении этого фрагмента выясняется, что имя на букву 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 цифр. Однако существует много альтернативных способов представления числа. Код города может быть заключен в круглые скобки и обычно (хотя и не всегда) отделяется от остального номера пробелом, точкой или дефисом. Код станции обычно (но тоже не всегда) отделяется от локального номера пробелом, точкой или дефисом. Таким образом, любая из следующих форм записи телефонного номера является допустимой:
7735555253 773-555-5253 (773)555-5253 (773) 555 5253 773.555.5253
С подобными нежесткими схемами записи легко работать при помощи регулярных выражений, но очень трудно без них. Мы воспользуемся функцией REGEXP_SUBSTR для извлечения телефонного номера из строки с контактными данными:
DECLARE
contact_info VARCHAR2(200) := '
address:
1060 W. Addison St.
Chicago, IL 60613
home 773-555-5253
';
phone_pattern VARCHAR2(90) :=
'\(?\d{3}\)?[[:space:]\.\-]?\d{3}[[:space:]\.\-]?\d{4}';
BEGIN
DBMS_OUTPUT.PUT_LINE('Номер телефона: '||
REGEXP_SUBSTR(contact_info,phone_pattern,1,1));
END;
Результат работы программы:
Номер телефона: 773-555-5253
Ого! Шаблон получился довольно устрашающим. Давайте разобьем его на составляющие:
\(?Шаблон начинается с необязательного символа открывающей круглой скобки. Так как круглые скобки в языке регулярных выражений являются метасимволами (то есть имеют специальное значение), для использования в качестве литерала символ круглой скобки необходимо экранировать (escape), поставив перед ним символ \ (обратная косая черта). Вопросительный знак — квантификатор, обозначающий ноль или одно вхождение предшествующего символа. Таким образом, эта часть выражения описывает необязательный символ открывающей круглой скобки.-
\d{3} \d— один из операторов, появившихся в Oracle10g Release 2 под влиянием языка Perl. Он обозначает произвольную цифру. Квантификатор {} указывает, что предшествующий символ входит в шаблон заданное количество раз (в данном случае три). Эта часть шаблона описывает три цифры. \)?Необязательный символ закрывающей круглой скобки.-
[[:space:]\.\-]?В квадратных скобках перечисляются символы, для которых обнаруживается совпадение — в данном случае это пропуск, точка или дефис. Конструкция [:space:] обозначает символьный классPOSIXдля пропускных символов (пробел, табуляция, новая строка) в текущем наборе NLS. Точка и дефис являются метасимволами, поэтому в шаблоне их необходимо экранировать обратной косой чертой. Наконец, ? означает ноль или одно вхождение предшествующего символа. Эта часть шаблона описывает необязательный пропуск, точку или дефис. \d{3}Эта часть шаблона описывает три цифры (см. выше).[[:space:]\.\-]?Эта часть шаблона описывает необязательный пропуск, точку или дефис (см. выше).\d{4}Четыре цифры (см. выше).
Обязательно комментируйте свой код, использующий регулярные выражения, — это пригодится тому, кто будет разбираться в нем (вполне возможно, это будете вы сами через полгода).
Общий синтаксис REGEXP_SUBSTR:
REGEXP_SUBSTR (исходная_строка, шаблон [,начальная_позиция [,номер [,модификаторы [,подвыражение]]]])
Функция REGEXP_SUBSTR возвращает часть исходной_строки, совпадающую с шаблоном или подвыражением. Если совпадение не обнаружено, функция возвращает NULL. Здесь исходная_строка — строка, в которой выполняется поиск; шаблон — регулярное выражение, совпадение которого ищется в исходной_строке; начальная_позиция — позиция, с которой начинается поиск; номер — порядковый номер совпадения (1 = первое, 2 = второе и т. д.); модификаторы — один или несколько модификаторов, управляющих процессом поиска.
Начиная с Oracle11g, также можно задать параметр подвыражение (1 = первое, 2 = второе и т. д.), чтобы функция возвращала начальную позицию заданного подвыражения (части шаблона, заключенной в круглые скобки). Подвыражения удобны в тех случаях, когда требуется найти совпадение для всего шаблона, но получить совпадение только для его части. Скажем, если мы хотим найти телефонный номер и извлечь из него код города, мы заключаем часть шаблона, описывающую код города, в круглые скобки, превращая ее в подвыражение:
DECLARE
contact_info VARCHAR2(200) := '
address:
1060 W. Addison St.
Chicago, IL 60613
home 773-555-5253
work (312) 555-1234
cell 224.555.2233
';
phone_pattern VARCHAR2(90) :=
'\(?(\d{3})\)?[[:space:]\.\-]?\d{3}[[:space:]\.\-]?\d{4}';
contains_phone_nbr BOOLEAN;
phone_number VARCHAR2(15);
phone_counter NUMBER;
area_code VARCHAR2(3);
BEGIN
contains_phone_nbr := REGEXP_LIKE(contact_info,phone_pattern);
IF contains_phone_nbr THEN
phone_counter := 1;
DBMS_OUTPUT.PUT_LINE('Номера:');
LOOP
phone_number := REGEXP_SUBSTR(contact_info,phone_pattern,1,phone_counter);
EXIT WHEN phone_number IS NULL; -- NULL означает отсутствие совпадений
DBMS_OUTPUT.PUT_LINE(phone_number);
phone_counter := phone_counter + 1;
END LOOP;
phone_counter := 1;
DBMS_OUTPUT.PUT_LINE('Коды городов:');
LOOP
area_code := REGEXP_SUBSTR(contact_info,phone_pattern,1,phone_
counter,'i',1);
EXIT WHEN area_code IS NULL;
DBMS_OUTPUT.PUT_LINE(area_code);
phone_counter := phone_counter + 1;
END LOOP;
END IF;
END;
Этот фрагмент выводит телефонные номера и коды городов:
Номера:
773-555-5253
(312) 555-1234
224.555.2233
Коды городов:
773
312
224
Подсчет совпадений
Еще одна типичная задача — подсчет количества совпадений регулярного выражения в строке. До выхода Oracle11g программисту приходилось в цикле перебирать и подсчитывать совпадения. Теперь для этого можно воспользоваться новой функцией REGEXP_COUNT. Общий синтаксис ее вызова:
REGEXP_COUNT (исходная_строка, шаблон [,начальная_позиция [,модификаторы ]])
Здесь исходная_строка — строка, в которой выполняется поиск; шаблон — регулярное выражение, совпадение которого ищется в исходной_строке; начальная_позиция — позиция, с которой начинается поиск; модификаторы — один или несколько модификаторов, управляющих процессом поиска.
DECLARE
contact_info VARCHAR2(200) := '
address:
1060 W. Addison St.
Chicago, IL 60613
home 773-555-5253
work (312) 123-4567';
phone_pattern VARCHAR2(90) :=
'\(?(\d{3})\)?[[:space:]\.\-]?(\d{3})[[:space:]\.\-]?\d{4}';
BEGIN
DBMS_OUTPUT.PUT_LINE('Обнаружено '
||REGEXP_COUNT(contact_info,phone_pattern)
||' телефонных номера');
END;
Результат:
Обнаружено 2 телефонных номера
Замена текста REGEXP_REPLACE
Поиск и замена — одна из лучших областей применения регулярных выражений. Текст замены может включать ссылки на части исходного выражения (называемые обратными ссылками), открывающие чрезвычайно мощные возможности при работе с текстом. Допустим, имеется список имен, разделенный запятыми, и его содержимое необходимо вывести по два имени в строке. Одно из решений заключается в том, чтобы заменить каждую вторую запятую символом новой строки. Сделать это при помощи стандартной функции REPLACE нелегко, но с функцией REGEXP_REPLACE задача решается просто. Общий синтаксис ее вызова:
REGEXP_REPLACE (исходная_строка, шаблон [,строка_замены [,начальная_позиция [,номер [,модификаторы ]]])
Здесь исходная_строка — строка, в которой выполняется поиск; шаблон — регулярное выражение, совпадение которого ищется в исходной_строке; начальная_позиция — позиция, с которой начинается поиск; модификаторы — один или несколько модификаторов, управляющих процессом поиска. Пример:
DECLARE
names VARCHAR2(60) := 'Anna,Matt,Joe,Nathan,Andrew,Jeff,Aaron';
names_adjusted VARCHAR2(61);
comma_delimited BOOLEAN;
extracted_name VARCHAR2(60);
name_counter NUMBER;
BEGIN
-- Искать совпадение шаблона
comma_delimited := REGEXP_LIKE(names,'^([a-z ]*,)+([a-z ]*){1}$', 'i');
-- Продолжать, только если мы действительно
-- работаем со списком, разделенным запятыми.
IF comma_delimited THEN
names := REGEXP_REPLACE(
names,
'([a-z A-Z]*),([a-z A-Z]*),',
'\1,\2' || chr(10) );
END IF;
DBMS_OUTPUT.PUT_LINE(names);
END;
Результат выглядит так:
Anna,Matt Joe,Nathan Andrew,Jeff Aaron
При вызове функции 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,
Два подвыражения соответствуют именам «Anna» и «Matt». В основе нашего решения лежит возможность ссылаться на текст, совпавший с заданным подвыражением, через обратную ссылку. Обратные ссылки \1 и \2 в тексте замены ссылаются на текст, совпавший с первым и вторым подвыражением. Вот что происходит:
'\1,\2' || chr(10) -- Текст замены
'Anna,\2' || chr(10) -- Подстановка текста, совпавшего
-- с первым подвыражением
'Anna,Matt' || chr(10) -- Подстановка текста, совпавшего
-- со вторым подвыражением
Вероятно, вы уже видите, какие мощные инструменты оказались в вашем распоряжении. Запятые из исходного текста попросту не используются. Мы берем текст, совпавший с двумя подвыражениями (имена «Anna» и «Matt»), и вставляем их в новую строку с одной запятой и одним символом новой строки.
Но и это еще не все! Текст замены легко изменить так, чтобы вместо запятой в нем использовался символ табуляции (ASCII-код 9):
names := REGEXP_REPLACE( names, '([a-z A-Z]*),([a-z A-Z]*),', '\1' || chr(9) || '\2' || chr(10) );
Теперь результаты выводятся в два аккуратных столбца:
Anna Matt Joe Nathan Andrew Jeff Aaron
Поиск и замена с использованием регулярных выражений — замечательная штука. Это мощный и элегантный механизм, с помощью которого можно сделать очень многое.
Максимализм и минимализм
Концепции максимализма и минимализма играют важную роль при написании регулярных выражений. Допустим, из разделенного запятыми списка имен нужно извлечь только первое имя и следующую за ним запятую. Список, уже приводившийся ранее, выглядит так:
names VARCHAR2(60) := 'Anna,Matt,Joe,Nathan,Andrew,Jeff,Aaron';
Казалось бы, нужно искать серию символов, завершающуюся запятой:
.*,
Давайте посмотрим, что из этого получится:
DECLARE names VARCHAR2(60) := 'Anna,Matt,Joe,Nathan,Andrew,Jeff,Aaron'; BEGIN DBMS_OUTPUT.PUT_LINE( REGEXP_SUBSTR(names, '.*,') ); END;
Результат выглядит так:
Anna,Matt,Joe,Nathan,Andrew,Jeff,
Совсем не то. Что произошло? Дело в «жадности» регулярных выражений: для каждого элемента регулярного выражения подыскивается максимальное совпадение, состоящее из как можно большего количества символов. Когда мы с вами видим конструкцию:
.*,
у нас появляется естественное желание остановиться у первой запятой и вернуть строку «Anna,». Однако база данных пытается найти самую длинную серию символов, завершающуюся запятой; база данных останавливается не на первой запятой, а на последней.
В версии Oracle Database 10g Release 1, в которой впервые была представлена поддержка регулярных выражений, возможности решения проблем максимализма были весьма ограничены. Иногда проблему удавалось решить изменением формулировки регулярного выражения — например, для выделения первого имени с завершающей запятой можно использовать выражение [^,]*,. Однако в других ситуациях приходилось менять весь подход к решению, часто вплоть до применения совершенно других функций.
Начиная с Oracle Database 10g Release 2, проблема максимализма отчасти упростилась с введением минимальных квантификаторов (по образцу тех, которые поддерживаются в Perl). Добавляя вопросительный знак к квантификатору после точки, то есть превращая * в *?, я ищу самую короткую последовательность символов перед запятой:
DECLARE names VARCHAR2(60) := 'Anna,Matt,Joe,Nathan,Andrew,Jeff,Aaron'; BEGIN DBMS_OUTPUT.PUT_LINE( REGEXP_SUBSTR(names, '(.*?,)') ); END;
Теперь результат выглядит так, как и ожидалось:
Anna,
Минимальные квантификаторы останавливаются на первом подходящем совпадении, не пытаясь захватить как можно больше символов.
Подробнее о регулярных выражениях
Регулярные выражения на первый взгляд просты, но эта область на удивление глубока и нетривиальна. Они достаточно просты, чтобы вы начали пользоваться ими после прочтения этой статьи (хочется надеяться!), и все же вам предстоит очень много узнать. Некоторые источники информации от компании Oracle и издательства O’Reilly:
- Oracle Database Application Developer’s Guide — Fundamentals. В главе 4 этого руководства описана поддержка регулярных выражений в Oracle.
- Oracle Regular Expression Pocket Reference
.Хороший вводный учебник по работе с регулярными выражениями; авторы — Джонатан Дженник ( Jonathan Gennick) и Питер Линсли (Peter Linsley). Питер является одним из разработчиков реализации регулярных выражений Oracle. - Mastering Oracle SQL. Одна из глав книги посвящена регулярным выражениям в контексте Oracle SQL. Отличный учебник для всех, кто хочет поднять свои навыки использования SQL на новый уровень.
- Mastering Regular Expressions. Книга Джеффри Фридла ( Jeffrey Friedl) считается самым авторитетным источником информации об использовании регулярных выражений. Если вы хотите действительно глубоко изучить материал — читайте книгу Фридла.
