Регулярні вирази, удосконалюють 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. Вони дійсно схожі на існуючі оператор і функції, однак тепер надаються потужні можливості зіставлення з шаблоном. Шукані дані можуть бути простою рядком або текстом великого обсягу, що зберігається в символьному стовпці бази даних. Регулярні вирази дозволяють шукати, замінювати і перевіряти дані способом, про який раніше і не мріяли, з високим ступенем гнучкості.


Приклади регулярних виразів


Перед використанням нової функціональності необхідно зрозуміти значення деяких метасимволів. Період (.) В регулярному виразі відповідає будь-якому символу (за винятком переходу на новий рядок). Наприклад, регулярний вираз ab відповідає рядку, що містить букву a, за якою слідує один будь-який символ (за винятком переходу на новий рядок), за яким слід буква b. Рядки axb, xaybx і abba підходять, оскільки містять цей шаблон. Якщо потрібне точне відповідність трехсімвольной рядку, в якій рядок починається з a і закінчується b, регулярне вираження необхідно прив’язати. Метасимвол вставки (^) Позначає початок рядка, а долар ($) – кінець рядка (див. Таблицю 1). Тому регулярному вираженню ^ ab $ відповідають рядки 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:]] шукає відповідність, яке починається з цифри. Дефіс (-) означає діапазон; регулярний вираз [am] відповідає будь-яким буквах від 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: квантіфікатори і оператори повтору























Квантіфікатор Опис
* Зустрічається 0 і більше разів
? Зустрічається 0 або 1 раз
+ Зустрічається 1 і більше разів
{m} Зустрічається рівно m раз
{m,} Зустрічається принаймні m раз
{m, n} Зустрічається принаймні m раз, але не більше n разів


Таблиця 3: Визначені символьні класи POSIX
































Клас символів Опис
[:alpha:] Букви
[:lower:] Букви в нижньому регістрі
[:upper:]

Літери у верхньому регістрі

[:digit:] Цифри
[:alnum:] Букви і цифри
[:space:] Прогалини (не друковані символи), такі як переклад каретки, новий рядок, вертикальна табуляція і подача сторінки
[:punct:]

Знаки пунктуації

[:cntrl:] Керуючі символи (не друкуються)
[:print:] Друковані символи


Таблиця 4: Альтернативне зіставлення і угрупування виразів

















Метасимвол Опис
/ Альтернатива Розділяє альтернативні варіанти, часто використовується з оператором угруповання ()
( ) Група Групує подвираженія для альтернативи, квантіфікатора або посилальної (див. розділ “Посилання”)
[char] Список символів Позначає список символів; більшість метасимволів в списку символів є літери, за винятком символьних класів і метасимволів ^ і –


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








Синтаксис Опис
REGEXP_LIKE (ісходная_строка, шаблон [, параметр_сопоставленія]) “Ісходная_строка” підтримує символьні типи даних (CHAR, VARCHAR2, CLOB, NCHAR, NVARCHAR2 і NCLOB, але не LONG). Параметр “шаблон” – це інша назва регулярного виразу. “Параметр_сопоставленія” дозволяє використовувати додаткові параметри, такі як символ переходу на новий рядок, багаторядкове форматування та забезпечення управління урахуванням регістра.


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








Синтаксис Опис
REGEXP_INSTR (ісходная_строка, шаблон
[, Начальная_позіція
[, Входження
[, Опція_возврата
[, Параметр_сопоставленія]]]])
Ця функція шукає за шаблоном і повертає першу позицію. Додатково можна вказати параметр “начальная_позіція”, з якою повинен починатися пошук. Параметр “входження” за замовчуванням має значення 1, якщо користувач не вкаже пошук послідовних входжень. Значення за замовчуванням для параметра “опція_возврата” – це 0, тоді повернеться початкова позиція шаблону; при значенні 1 повертається позиція символу, наступного за шаблоном.


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















































Синтаксис Опис
  Пробіл, який повинен бути зіставлений
[:digit:] Числовий клас POSIX
] Кінець списку символів
{5} Повторювати список символів рівно п’ять разів
( Початок подвираженія
Літера дефіс, так як він не є метасимволом діапазону всередині списку символів
[ Початок списку символів
[:digit:] Клас POSIX [: digit:]
[ Початок списку символів
] Кінець списку символів
{4} Повторювати список символів рівно чотири рази
) Закриває дужка – кінець подвираженія
? Квантіфікатор? відноситься до групового подвираженій 0 або 1 раз, в результаті чого 4-цифровий код стає додатковим
$ Метасимвол прив’язки, щоб показати кінець рядка


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








Синтаксис Опис
REGEXP_SUBSTR (ісходная_строка, шаблон
[, Позиція [, входження
[, Параметр_сопоставленія]]])
Функція REGEXP_SUBSTR повертає підрядок, яка відповідає шаблоном.


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








Синтаксис Опис
REGEXP_REPLACE (ісходная_строка, шаблон
[, Строка_замени [, позиція
[, Входження, [параметр_сопоставленія]]]])
Ця функція замінює підрядок, відповідну шаблоном на задану “строку_замени”, використовуючи складні операції пошуку-і-заміни.


Таблиця 10: Метасимвол посилання









Метасимвол Опис
digit Зворотній слеш За нею слідує цифра від 1 до 9, зворотна коса риска пов’язана з попереднім зіставленням з відповідним номером укладеного в дужки подвираженія.

(Зауважте: Зворотній коса риса може мати інше значення в регулярному виразі; в залежності від контексту вона може означати також символ Escape


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















































Елемент регулярного вираження Опис

(

Початок першого подвираженія
. Відповідність одному будь-якому символу, за винятком переходу на новий рядок
* Оператор повтору відповідає попередньому метасимволи. від 0 до n разів
) Кінець першого подвираженія; результат зіставлення зберігається в 1

(У цьому прикладі, Ellen.)

  Пробіл, який повинен бути зіставлений
( Початок другого подвираженія
. Зіставлення з одним будь-яким символом, за винятком переходу на новий рядок
* Оператор повтору відповідає попередньому метасимволи. від 0 до n разів
) Кінець другого подвираженія; результат зіставлення зберігається в 2

(У цьому прикладі, Hildi.)

  Пробіл
( Початок третього подвираженія
. Зіставлення з одним будь-яким символом, за винятком переходу на новий рядок
* Оператор повтору відповідає попередньому метасимволи. від 0 до n разів
) Кінець третього подвираженія; результат зіставлення зберігається в 3

(У цьому прикладі, Smith.)


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










































































Елемент регулярного вираження Опис
^ Початок рядка символів (Регулярний вираз не може мати лідируючих символів перед зіставленням.)
( Початок подвираженія і список альтернатив, розділених метасимволом /
[ Початок списку символів
[:digit:] Числовий клас POSIX
] Кінець списку символів
{3} Повтор списку символів рівно три рази
Дефіс
[ Початок списку символів
[:digit:] Числовий клас POSIX
] Кінець списку символів
{2} Повтор списку символів рівно два рази
Інший дефіс
[ Початок списку символів
[:digit:] Числовий клас POSIX
] Кінець списку символів
{4} Повтор списку символів рівно чотири рази
/ Метасимвол альтернативи; кінець першого варіанту і початок наступного альтернативного вираження
[ Початок списку символів
[:digit:] Числовий клас POSIX
]

Кінець списку символів

{9} Повтор списку символів рівно дев’ять разів
) Закриває дужка, закриває групу подвираженія, використовуваного як альтернатива
$ Метасимвол прив’язки, показує кінець рядка; додаткові символи після шаблона не допускаються

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*