Регулярные выражения, усовершенствующие SQL-предложения

Регулярные выражения в Oracle Database 10g - это мощный инструмент для манипулирования текстовыми данными.

Новая возможность Oracle Database 10g значительно увеличивает способность поиска и манипулирования символьными данными. Эта возможность, регулярные выражения, это транскрипция для описания образца текста. Она давно имеется во многих языках программирования и множестве утилит для UNIX.

Регулярные выражения в Oracle реализованы в виде SQL-функций и оператора выражения WHERE. Тем, кто еще ничего не знает о регулярных выражениях, эта статья может дать общее понятие об этой новой и весьма мощной, но пока кажущейся непонятной, возможности. Читатели, которые уже познакомились с регулярными выражениями, могут пополнить знания о том, как применить эту функциональность в контексте языка Oracle SQL.

Что такое регулярное выражение?

Регулярное выражение содержит один и более символов и/или метасимволов. В самом простом виде регулярное выражение может состоять только из символов, например, регулярное выражение cat. Оно читается как буква c, за которой следует буква a и t, и этому шаблону соответствуют такие строки, как cat, location и catalog. Метасимволы обеспечивают алгоритмы обработки в Oracle символов, из которых состоит регулярное выражение. Когда значение различных метасимволов будет понятным, вы увидите, как удобны регулярные выражения для выделения и замены каких-либо текстовых данных.

Проверка данных, поиск дубликатов, обнаружение лишних пробелов или разбор строки - это некоторые из многих примеров использования регулярных выражений. Их можно применять для проверки формата телефонного номера, zip-кода, email-адресов, номеров социального обеспечения, IP-адресов, имен файлов и директорий и так далее. Кроме того, можно искать комбинации, например, HTML-тегов, чисел, дат, и другое, которые соответствуют какому-либо шаблону в тексте, и заменять их другим набором символов.

Использование регулярных выражений в Oracle Database 10g

Чтобы воспользоваться возможностями регулярных выражений, можно применить функции REGEXP_INSTR, REGEXP_SUBSTR и REGEXP_REPLACE и новый оператор Oracle SQL - REGEXP_LIKE. Вы увидите, как эта новая функциональность поддерживает существующий оператор LIKE и функции INSTR, SUBSTR и REPLACE. Они действительно похожи на существующие оператор и функции, однако теперь предоставляются мощные возможности сопоставления с шаблоном. Искомые данные могут быть простой строкой или текстом большого объема, хранимым в символьном столбце базы данных. Регулярные выражения позволяют искать, заменять и проверять данные способом, о котором ранее и не мечтали, с высокой степенью гибкости.

Примеры регулярных выражений

Перед использованием новой функциональности необходимо понять значение некоторых метасимволов. Период (.) в регулярном выражении соответствует любому символу (за исключением перехода на новую строку). Например, регулярное выражение a.b соответствует строке, содержащей букву a, за которой следует один любой символ (за исключением перехода на новую строку), за которым следует буква b. Строки axb, xaybx и abba подходят, так как содержат этот шаблон. Если требуется точное соответствие трехсимвольной строке, в которой строка начинается с a и заканчивается b, регулярное выражение необходимо привязать. Метасимвол вставки (^) обозначает начало строки, а доллар ($) - конец строки (см. Таблицу 1). Поэтому регулярному выражению ^a.b$ соответствуют строки aab, abb или axb. Чтобы сравнить этот метод со знакомым сопоставлением с шаблоном оператора LIKE, можно использовать такой шаблон как a_b, где подчеркивание (_) - это любой одиночный символ.

По умолчанию отдельный символ или список символов регулярного выражения сопоставляются один раз. Чтобы найти несколько вхождений символа регулярного выражения, применяется квантификатор, называемый также оператором повтора. Если требуется соответствие строке, которая начинается с буквы a и заканчивается буквой b, регулярное выражение выглядит следующим образом: ^a.*b$. Метасимвол * повторяет предыдущее соответствие любому метасимволу (.) ноль, один и более раз. Эквивалентный шаблон оператора LIKE - это a%b, где (%) обозначает ноль, одно и более вхождений любых символов.

В Таблице 2 показан полный список операторов повтора. Заметьте, что в ней содержатся специфичные варианты повтора, которые допускают больше гибкости, чем существующие групповые символы оператора LIKE. Если выражение заключить в скобки, в результате чего образуется подвыражение, то подвыражение может повторяться заданное число раз. Например, регулярному выражению b(an)*a соответствует ba, bana, banana, yourbananasplit, и так далее.

Регулярные выражения Oracle поддерживают символьные классы POSIX (Portable Operating System Interface), которые перечислены в Таблице 3. Это значит, что можно искать совершенно особые типы символов. Представьте написание условия с оператором LIKE, которое ищет только символы, не являющиеся буквами - получающееся выражение WHERE легко становится очень сложным.

Символьный класс POSIX должен входить в список символов, обозначаемый квадратными скобками ([]). Например, регулярное выражение [[:lower:]] соответствует одному символу в нижнем регистре, а [[:lower:]]{5} - пяти последовательным символам в нижнем регистре.

Кроме символьных классов POSIX, в список символов можно включать отдельные символы. Например, регулярное выражение ^ab[cd]ef$ соответствует строкам abcef и abdef. Сопоставляются как c, так и d.

Большинство метасимволов списка понимаются как литеры, за исключением вставки (^) и дефиса (-). Регулярные выражения могут казаться сложными, так как одни и те же метасимволы имеют несколько значений в зависимости от контекста. ^ как раз один из таких метасимволов. Если он - первый символ в списке символов, то означает не вхождение в список. Поэтому, [^[:digit:]] ищет соответствие, состоящее только из любых нецифровых символов, в то время как ^[[:digit:]] ищет соответствие, которое начинается с цифры. Дефис (-) обозначает диапазон; регулярное выражение [a-m] соответствует любым буквам от a до m. Однако он является литерой дефис, если находится в начале списка символов, например [-afg].

Один из предыдущих примеров показывал использование скобок для создания подвыражения; они позволяют ввести альтернативы, разделенные вертикальной чертой (/) - метасимволом альтернативы.

Например, регулярное выражение t(a/e/i)n допускает три возможных альтернативных символа между буквами t и n. Этому шаблону соответствуют слова tan, ten, tin и Pakistan, но не teen, mountain или tune. С другой стороны, регулярное выражение t(a/e/i)n можно описать как список символов, например t[aei]n. Эти метасимволы сгруппированы в Таблице 4. Хотя метасимволов значительно больше, этот краткий список важен для понимания регулярных выражений, используемых в настоящей статье.

Оператор REGEXP_LIKE

Когда в базе данных Oracle встречается оператор REGEXP_LIKE, он знакомит с регулярными выражениями. В таблице 5 показан синтаксис REGEXP_LIKE.

Следующий SQL-запрос с выражением WHERE демонстрирует оператор REGEXP_LIKE, который ищет столбец ZIP со значениями, которые удовлетворяют регулярному выражению [^[:digit:]]. Отбираться будут те строки таблицы ZIPCODE, в которых значение столбца ZIP содержит любой символ, не являющийся цифрой.

SELECT zip
 FROM zipcode
 WHERE REGEXP_LIKE(zip, '[^[:digit:]]');

ZIP
-----
ab123
123xy
007ab
abcxy

Этот пример регулярного выражения состоит только из метасимволов, а точнее, из символьного класса POSIX digit, ограниченного двоеточиями и квадратными скобками. Второй набор скобок (как в [^[:digit:]]) ограничивает список символьных классов. Как пояснялось выше, это необходимо, так как символьные классы POSIX могут использоваться только для формирования списка символов.

Функция REGEXP_INSTR

Эта функция возвращает начальную позицию образца, поэтому она работает примерно так, как уже знакомая функция INSTR. Синтаксис новой функции REGEXP_INSTR показан в Таблице 6. Основное различие между этими двумя функциями заключается в том, что REGEXP_INSTR позволяет указать шаблон вместо конкретной строки поиска; обеспечивая, таким образом, большее многообразие. Следующий пример использует функцию REGEXP_INSTR, которая возвращает начальную позицию пятицифрового zip-кода в строке Joe Smith, 10045 Berry Lane, San Joseph, CA 91234. Если регулярное выражение выглядит как [[:digit:]]{5}, то вместо zip-кода возвратится начальная позиция номера дома, так как 10045 - это первое вхождение из пяти последовательных цифр. Поэтому выражение необходимо привязать к концу строки метасимволом $, тогда функция отобразит начальную позицию zip-кода вместо набора цифр, соответствующих номеру дома.

SELECT REGEXP_INSTR('Joe Smith, 10045 Berry Lane, San Joseph, CA 91234',
 '[[:digit:]]{5}$')
 AS rx_instr
 FROM dual;

 RX_INSTR
----------
 45

Более сложные шаблоны

Давайте усложним шаблон zip-кода предыдущего примера, чтобы найти дополнительные четыре цифры. Теперь он может выглядеть так: [[:digit:]]{5}(-[[:digit:]]{4})?$. Если исходная строка заканчивается пятицифровым zip-кодом или 5-цифровым + 4 zip-кодом, то надо будет показать начальную позицию шаблона.

SELECT REGEXP_INSTR(
'Joe Smith, 10045 Berry Lane, San Joseph, CA 91234-1234',
 ' [[:digit:]]{5}(-[[:digit:]]{4})?$')
 AS starts_at
 FROM dual;

 STARTS_AT
----------
 44

В этом примере в скобки заключено подвыражение (-[[:digit:]]{4}), которое может повторяться ноль и более раз, что показывает оператор повтора? С другой стороны, если попытаться использовать традиционные SQL-функции для получения того же результата, то формулировка будет сложна даже для знатока SQL. Чтобы лучше объяснить различные компоненты этого примера регулярного выражения, в Таблице 7 представлено описание отдельных литер и метасимволов.

Функция REGEXP_SUBSTR

Функция REGEXP_SUBSTR, очень похожа на функцию SUBSTR и возвращает часть строки. В Таблице 8 показан синтаксис новой функции. В следующем примере возвращается строка, которая соответствует шаблону [^,]*. Регулярное выражение ищет запятую, за которой следует пробел; затем ноль и более символов, не являющихся запятыми, что показывает [^,]*; и затем ищет другую запятую. Шаблон будет выглядеть примерно как строка значений, разделенных запятыми.

SELECT REGEXP_SUBSTR('first field, second field , third field',
 ', [^,]*,')
 FROM dual;

REGEXP_SUBSTR('FIR
------------------
, second field ,

Функция REGEXP_REPLACE

Сначала рассмотрим традиционную SQL-функцию REPLACE, которая заменяет одну строку другой. Предположим, что в тексте имеются дополнительные пробелы, а вам хотелось бы заменить их одним пробелом. В функции REPLACE необходимо перечислить ровно столько пробелов, сколько надо заменить. Однако дополнительные пробелы могут располагаться не только в одном месте. Следующий пример имеет три пробела между Joe и Smith. Параметр функции REPLACE показывает, что два пробела следует заменить одним пробелом. В этом случае результат имеет на один дополнительный пробел меньше, в то время как в исходной строке между Joe и Smith было три пробела.

SELECT REPLACE('Joe Smith',' ', ' ')
 AS replace
 FROM dual;

REPLACE
---------
Joe Smith

Функция REGEXP_REPLACE выполняет замены на порядок дальше; она описана в Таблице 9. Следующий запрос заменяет два и более любых пробелов на один пробел. Подвыражение ( ) содержит один пробел, который может повторяться два и более раз, что показывает {2,}.

SELECT REGEXP_REPLACE('Joe Smith',
 '( ){2,}', ' ')
 AS RX_REPLACE
 FROM dual;

RX_REPLACE
----------
Joe Smith

Полезная возможность регулярных выражений - это способность запоминать подвыражение для дальнейшего использования; она называется также ссылочность (описана в Таблице 10). Она позволяет выполнять сложные замены, такие как перемещение образца на новую позицию или нахождение повторяющегося слова или буквы. Соответствующая подвыражению часть сохраняется во временном буфере. Буферы нумеруются слева направо, а доступ к ним осуществляется через \цифра, где цифра, это число от 1 до 9, которое соответствует порядковому номеру подвыражения, обозначенному скобками.

Следующий пример показывает, как имя Ellen Hildi Smith преобразовывается в Smith, Ellen Hildi с помощью ссылок на отдельные подвыражения по номеру.

SELECT REGEXP_REPLACE(
 'Ellen Hildi Smith',
 '(.*) (.*) (.*)', '\3, \1 \2')
 FROM dual;

REGEXP_REPLACE('EL
------------------
Smith, Ellen Hildi

В этом SQL-предложении есть три отдельных подвыражения, заключенных в скобки. Каждое отдельное подвыражение содержит метасимвол соответствия любому символу (.), за которым следует метасимвол *, показывающий, что любой символ (за исключением перехода на новую строку) должен появиться ноль и более раз. Каждое подвыражение разделяет пробел, который также должен быть сопоставлен. Скобки обозначают подвыражения для фиксации значений, на которые можно ссылаться через \цифра. Первое подвыражение связывается с \1, второе \2 и так далее. Эти ссылки используются в последнем параметре этой функции (\3, \1 \2), которая возвращает измененную подстроку, преобразовав ее в требуемый формат (включая запятую и пробелы). В Таблице 11 подробно описываются отдельные элементы этого регулярного выражения.

Ссылки полезны для замены, форматирования и нахождения подстроки, и могут применяться для поиска расположенных рядом значений. Следующий пример показывает использование функции REGEXP_SUBSTR для поиска дубликатов символьно-цифровых значений, разделенных пробелом. Полученный результат показывает слова, которые продублированы.

SELECT REGEXP_SUBSTR(
 'The final test is is the implementation',
 '([[:alnum:]]+)([[:space:]]+)\1') AS substr
 FROM dual;

SUBSTR
------
is is

Дополнительный параметр сопоставления

Вы, возможно, заметили, что оператор и функции регулярного выражения могут содержать дополнительный параметр сопоставления. Этот параметр управляет учетом регистра, переходом на новую строку и многострочными вхождениями повторов.

Практические приложения регулярных выражений

Регулярные выражения используются не только в запросах, но также в любом месте, где допускаются SQL-операторы и функции, например в PL/SQL. Можно написать триггеры, в которых регулярные выражения полезно использовать для проверки, генерации или выделения значений.

Следующий пример показывает, как оператор REGEXP_LIKE можно применять в check-ограничении на столбец для контроля данных. Он проверяет корректность формата номера социального обеспечения при вставке или изменении записи. Для такого ограничения столбца допустим номер социального обеспечения в формате 123-45-6789 или 123456789. Корректные данные должны начинаться с трех цифр, за которыми следует дефис, две или более цифр, дефис и, наконец, другие четыре цифры. Альтернативное выражение допускает только девять последовательных цифр. Варианты разделяются вертикальной чертой (/).

ALTER TABLE students
 ADD CONSTRAINT stud_ssn_ck CHECK
 (REGEXP_LIKE(ssn,
 '^([[:digit:]]{3}-[[:digit:]]{2}-[[:digit:]]{4}/[[:digit:]]{9})$'));

Символы перед или после не допускаются, что показывают ^ и $. Необходимо убедиться, что регулярное выражение не разбито на несколько строк и не содержит лишних пробелов, если только не требуется, чтобы они были частью шаблона и поэтому сопоставлялись. В Таблице 12 объясняются отдельные элементы этого примера регулярного выражения.

Подробнее

Смотрите на странице the Oracle Database 10g:

/products/database/oracle10g/index.html

Сравнение регулярных выражений с существующей функциональностью

Регулярные выражения имеют некоторые преимущества перед обычным оператором LIKE и функциями INSTR, SUBSTR и REPLACE. Эти традиционные SQL-функции не имеют возможности сопоставления с шаблоном. Только оператор LIKE может выполнять символьное сопоставление с помощью символов группировки % и _, однако LIKE не поддерживает повторы выражения, сложное чередование, диапазоны символов, списки символов, символьные классы POSIX, и др. А новые функции с регулярными выражениями позволяют найти еще и дубликаты, и выполнить перестановку. Примеры, показанные в этой статье, позволяют кратко заглянуть в мир регулярных выражений и понять, как применять их в приложениях.

Полезное добавление к инструментарию

Регулярные выражения очень полезны, так как помогают решить сложные задачи. Некоторые возможности регулярных выражений трудно воспроизвести с помощью традиционных SQL-функций. Когда будет изучено создание основных конструкций этого несколько сложного языка, регулярные выражения станут необходимой частью инструмента в контексте не только SQL, но и других языков программирования. Хотя тестирование и допущение ошибок иногда необходимо для получения индивидуальных навыков написания шаблонов, элегантность и мощность регулярных выражений бесспорна.

Алиса Ришет ( ar280@yahoo.com ) работает в Database Application Development и Design track в Columbia University's Computer Technology and Application Program. Она автор Oracle SQL Interactive Workbook 2nd edition (Prentice Hall, 2002) и готовящейся в публикации Oracle SQL by Example (Prentice Hall, 2003). Ришет - архитектор баз данных, DBA и руководитель проекта с 15-летним стажем для Fortune 100 компаний и работает с Oracle, начиная с 5-ой версии.

Таблица 1: Метасимволы привязки

Таблица 2: Квантификаторы и операторы повтора

Таблица 3: Предопределенные символьные классы POSIX

Таблица 4: Альтернативное сопоставление и группировка выражений

Таблица 5: Оператор REGEXP_LIKE

Таблица 6: Функция REGEXP_INSTR

Таблица 7: Описание выражения для 5-цифрового + 4 Zip-кода

Таблица 8: Функция REGEXP_SUBSTR

Таблица 9: Функция REGEXP_REPLACE

Таблица 10: Метасимвол ссылки

Таблица 11: Описание регулярного выражения замены-по-шаблону

Таблица 12: Описание регулярного выражения для номера социального обеспечения