Регулярные выражения в Oracle 10g

1. РЕГУЛЯРНЫЕ ВЫРАЖЕНИЯ в ORACLE 10g

Ефимова Светлана, Агиветова Рахат
AT Consulting
Заполнить в Timesheet: HQ->

2. План

• Что такое регулярные выражения и для чего они
нужны?
• Функции регулярных выражений
– Функция regexp_like()
• Метасимволы в шаблонах
– Что такое шаблон?
– Что такое метасимвол?
– Закрепляющие метасимволы
– Метасимвол . (точка)
– Метасимволы, означающие количество
– Метасимвол отрицания ^ (знак крышки)
– Метасимволы диапазона символов [A-Z]
– Метасимволы группировки в шаблонах
– Классы символов POSIX в шаблонах
– Функция regexp_substr()
– Функция regexp_instr()
– Функция regexp_replace()
• Обратные ссылки в регулярных выражениях

3. Примеры решаемых задач

Необходимо вырезать из паспортных данных
дату выдачи паспорта.
Даты выдачи паспорта может иметь разные форматы.
PASSPORT
IDATE
10/05/2007 отд. по р. Западное Дегунино г.Москвы
10.05.2007
ОВД Московской области 12.11.2008 КП-77711
12.11.2008
10 ОВД МОСКВЫ 10.01.2001
10.01.2001
01.05.09 РОВД г. Домодедово
01.05.2009
РОВД Первомайского района г.Чебоксары 06.05.2009
06.05.2009
04/01/2005 ОВД района Южное Бутово г.Москвы
04.01.2005
выдан 30/01/2005 ОВД района Медведково г.Москвы
30.01.2005
select passport, regexp_substr(passport,
'[[:digit:]]{2,4}[./][[:digit:]]{2,4}[./]
[[:digit:]]{2,4}')
from customers

4. Примеры решаемых задач

Необходимо удалить из адреса лишние
пробелы.
Кол-во пробелов между словами разное.
ADDRESS
ADDRESS_CLEAR
г.Москва,
ул.Вятская,
Москва,
ул. Октябрьская,
МОСКВА,
Дмитровская,
г. Домодедово,
д.999,
кв.77777
г.Москва, ул.Вятская, д.999, кв.77777
17, кв 999 Москва, ул. Октябрьская, 17, кв 999
1009,
кв.
ул. Лесная,
11
д.14
МОСКВА, Дмитровская, 1009, кв. 11
г. Домодедово, ул. Лесная, д.14
г Москва, Алтуфьевское шоссе, д.66, кв.13
г Москва, Алтуфьевское шоссе, д.66, кв.13
г.Москва, проспект Вернадского,
г.Москва, проспект Вернадского, д.3, кв.3
Москва, ул. Спортивная,
д.3, кв.3
д.4,
кв.4
Москва, ул. Спортивная, д.4, кв.4
select address, regexp_replace(address, ' {2,}',' ')
from customers

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

Регулярные выражения реализованы
в виде простых функций, которые
позволяют очень легко выполнять
самые сложные манипуляции с
строковыми данными.
• Появились только начиная с Oracle 10g

6. Примеры использования

Возвращает список клиентов, фамилии которых
начинаются на буквы от А до N.
select * from customers
where regexp_like (full_name, '^[A-N]');
Переставляет слова в тексте.
select regexp_replace('Фамилия Имя Отчество', '([^
]+) ([^ ]+) ([^ ]+)', '\2 \3, \1') from dual
-----------------Имя Отчество, Фамилия

7. Функции регулярных выражений

regexp_like() - аналог оператора like
regexp_instr() – аналог функции instr()
regexp_substr() – аналог функции substr()
regexp_replace() – аналог функции replace()

8. Функция regexp_like()

Функция regexp_like() ищет подстроку
подходящую под указанный шаблон.
Возвращает true, если находит подстроку, false – если нет.
Похожа на оператор like, но имеет больше возможностей.
Синтаксис:
regexp_like (исходная_строка, шаблон)
шаблон – это и есть регулярное выражение
Используется только в разделе where.
Пример использования:
select * from customers
where regexp_like (full_name,'^S')

9. Что такое шаблон?

Шаблон (регулярное выражение):
• определяет, что именно нужно
вырезать/найти/заменить
• состоит из символов и метасимволов
Пример шаблона:
select * from customers
where regexp_like (full_name,'^S');
Поиск по шаблону выполняется по ВСЕМУ тексту, если
не используются закрепляющие символы ^ и $

10. Что такое метасимвол?

Метасимвол – это служебный символ, который
имеет значение отличное от своего
первоначального значения
Например, символ ^ является метасимволом в
регулярном выражении, так как означает
закрепление выражения на начало строки
Примеры метасимволов:
^, $, (, ), [, ] , ., *, + и тд.
Примеры обычных символов:
%, =, &, A, k, 0, 9 и тд.

11. Примеры использования

Поиск всех строк, содержащих букву S:
select * from customers
where regexp_like (full_name, 'S')
Поиск всех строк, начинающихся на букву S:
select * from customers
where regexp_like (full_name, '^S')
Поиск всех строк, заканчивающихся на букву S:
select * from customers
where regexp_like (full_name, 'S$')

12. Самостоятельное задание №0

Напишите SQL запрос,
выбирающий из таблицы
customers список клиентов,
ФИО которых:
1. Начинается на “K”
2. Которых зовут “Sveta”

13. Закрепляющие метасимволы

С помощью закрепляющих метасимволов,
можно указать, где именно – в начале или
в конце текста - нужно искать текст по
шаблону.
^ - закрепляет выражение на начале строки
$ - закрепляет выражение к концу строки
Поиск по шаблону выполняется по ВСЕМУ
тексту, если не используются закрепляющие
символы ^ и $

14. Самостоятельное задание №1

Напишите SQL запрос, выбирающий из
таблицы customers список клиентов,
ФИО которых:
1. Начинается на “Sa”
2. Заканчивается на “na”
3. Напишите эквивалент следующих
запросов с использованием регулярных
выражений:
select * from customers where full_name like 'S%';
select * from customers where full_name like '%S%';
select * from customers where full_name like '%S';

15. Метасимвол . (точка)

. (точка) – означает один любой
символ.
select * from customers
where regexp_like (phone_number,
'+7 ... .......');
Выводит список клиентов, номера телефонов
которых имеют формат +7, пробел, 3 любых
символа, пробел, затем 7 любых символа.
• Пробел НЕ является метасимволом.

16. Классы символов POSIX в шаблонах

Класс символов
Описание
[:alpha:]
Алфавитные символы
[:lower:]
Строчные алфавитные символы
[:upper:]
Прописные алфавитные символы (верхний регистр)
[:digit:]
Цифры
[:alnum:]
Буквенно-цифровые символы
[:space:]
Служебные символы(непечатные): пробел, возврат
каретки, новая строка, табуляция
[:punct:]
Пунктуационные символы
[:cntrl:]
Control-символы (непечатные)
[:print:]
Печатные символы
Классы символов POSIX используются в дополнительных
квадратных скобках, как например в [[:digit:]]:
select * from customers where regexp_like (passport,
'[[:digit:]][[:digit:]]/[[:digit:]][[:digit:]]/[[:
digit:]][[:digit:]][[:digit:]][[:digit:]]')
Возвращает список клиентов, паспортные данные которых
содержат дату выдачи в формате NN/NN/NNNN

17. Самостоятельное задание №2

1. Выведите список правильных индексов (zip)
из таблицы customers. Правильный индекс
состоит только из 6 цифр.
2. Выведите список клиентов, номера
телефонов которых имеет формат:
+7 NNN NNNNNNN; N – цифра.

18. Метасимволы, означающие количество (квантификаторы)

Квантификаторы – это метасимволы, которые
означают какое-то количество символов.
Квантификатор относится к предыдущему символу
или группе символов внутри скобок ( ).
Квантификатор
Значение
*
0 или больше раз
?
0 или 1 раз
+
1 или больше раз
{m}
ровно m раз
{m,}
m или более раз
{m, n}
от m до n раз
select * from customers where
regexp_like (zip, '^[[:digit:]]{6}$')
Возвращает список клиентов, индексы которых состоят
из 6 цифр.

19. Пример использования

select * from customers where regexp_like
(passport,
'[[:digit:]]{2}/[[:digit:]]{2}/[[:digit:]]{4}')
Возвращает список клиентов, паспортные данные
которых содержат дату выдачи в формате
NN/NN/NNNN.
select * from customers
where regexp_like (full_name, '^K.*a$');
Возвращает список клиентов, ФИО которых
начинается на K и закансивается на a.

20. Самостоятельное задание №3

Напишите следующие SQL запросы с
использованием квантификаторов:
1. Выведите список правильных индексов из
таблицы customers. Правильный индекс
состоит только из 6 цифр.
2. Выведите список клиентов, номера
телефонов которых имеет формат:
+7 NNN NNNNNNN; N – цифра.

21. Метасимвол отрицания ^

^ - отрицание
Так как этот же метасимвол ^ имеет и другое
значение (закрепление поиска на начале
строки), он используется ПЕРВЫМ В
КВАДРАТНЫХ СКОБКАХ, а за ним указывается
символ, который отрицается.
select * from customers where regexp_like (full_name, '[^S]');
выполняет поиск клиентов, имена которых содержит «не S»
отрицание с помощью ключевого слова not:
select * from customers
where not regexp_like (full_name, 'S');

22. Самостоятельное задание №4

1. Выведите список клиентов, ФИО которых
НЕ содержит S.

23. Примеры использования

select * from customers where regexp_like
(zip, '[^[:digit:]]');
Возвращает список клиентов, индексы
которых содержат НЕцифровые
символы:

24. Самостоятельное задание №4

1. Выведите список индексов, начинающихся
НЕ на цифру 0.

25. Метасимволы диапазона символов [A-Z]

Метасимвол «-» используется в квадратных
скобках для указания диапазона букв или
чисел.
[A-N] – диапазон букв от A до N
[0-5] – диапазон цифр от 0 до 5
select * from customers
where regexp_like (full_name, '^[A-N]');
Возвращает список клиентов, фамилии
которых начинаются на буквы от А до N

26. Самостоятельное задание №5

1. Выведите список клиентов, фамилии
которых начинаются на буквы от N до Z.
2. Выведите список клиентов, индексы
которых состоят из 6 цифр, первая цифра
должна быть больше или равно 1.

27. Метасимволы группировки в шаблонах

( ) – используется для группировки в
шаблонах
| – означает ИЛИ, используется с
метасимволом группировки ( )
select * from customers
where regexp_like (email, '(ru|net)$');
Возвращает список клиентов, email’ы
которых находятся в зоне ru или net.

28. Самостоятельное задание №6

Используя регулярные выражения,
выведите список клиентов:
1. email’ы которых находятся в зоне
.ru или .com и имеют правильный
формат, то есть:
Имеют формат
пользователь@домен.зона
Обязательно присутствует @ за
именем пользователя и перед
доменным именем

29. Список символов и экранирование

[ ] – в квадратных скобках указываются списки
символов, а также диапазоны букв или цифр
Экранирование – это использование метасимвола в качестве
обычного символа.
Для экранирования используются квадратные скобки [ ] или
обратный слэш \ перед метасимволом.
select * from customers where
regexp_like (phone_number, '[(].{3}[)]');
выводит список клиентов, номера телефонов которых
содержат (, затем, ровно через 3 символа содержат ).
Если в [ ] указано более одного символа, символы
имеют значение ИЛИ.
select * from customers where
regexp_like (phone_number, '[( ].{3}[) ]');
выводит список клиентов, номера телефонов, которых
содержат ( или пробел, затем, ровно через 3 символа
содержат ) или пробел.

30. Функция regexp_substr()

31. Функция regexp_substr()

Функция regexp_substr() возвращает
подстроку, подходящую под указанный шаблон.
Синтаксис:
regexp_substr (исходная_строка, шаблон)
Может использоваться в разделе select и
where.
Пример использования:
select regexp_substr(full_name, ' [^ ]* ')
from customers;

32. Пример использования regexp_substr()

select regexp_substr(full_name, ' [^ ]* ')
from customers;
FULL_NAME
ONLY NAME
Efimova Sveta Nikolaevna
Sveta
Klimov Igor Vladimirovich
Igor
Krysanova Elena Vladimirovna
Elena
Данный запрос вырезает имя из ФИО.
Предполагается, что имя идет за фамилией
после пробела.
Ищется пробел, потом 0 или больше символов,
дальше ищется еще один пробел.

33. Самостоятельное задание №7

Используя регулярные выражения,
1. выведите фамилию, имя и отчество
клиентов в отдельных столбцах, вырезав
их из столбца full_name, который
содержит ФИО.
FULL_NAME
Efimova Sveta Nikolaevna
Klimov Igor Vladimirovich
SURNAME
Efimova
Klimov
NAME ONAME
Sveta Nikolaevna
Igor
Vladimirovich

34. Дополнительные параметры функции regexp_substr()

regexp_substr (
исходная_строка, шаблон
[, позиция [, вхождение]])
Позиция – это стартовая позиция с которой начинается поиск.
Вхождение – номер вхождения.
select regexp_substr(full_name, '[^ ]+',1,3)
from customers t;
Данный запрос выводит третье вхождение подстроки, найденной
по шаблону '[^ ]+‘.
Значение шаблона: ищется 1 или более символов, которые НЕ
являются пробелами.

35. Пример использования regexp_substr() 2

select regexp_substr(address, ', [^,]*,') from
customers;
или
select regexp_substr(address, '[^,]+',1,2)
from customers;
ADDRESS
STREET
г Москва, Алтуфьевское шоссе, д.66, кв.13
, Алтуфьевское шоссе,
г.Москва, проспект Вернадского, д.3, кв.3
, проспект Вернадского,
Москва, ул. Спортивная, д.4, кв.4
, ул. Спортивная,
Первый запрос вырезает название улицы из строки адреса.
Предполагается, что название улицы идет за названием города, через
запятую.
Ищется запятая, за которой идет пробел (, ), потом 0 или больше
символов, но не запятых, ([^,]*); и, наконец, ищется еще одна запятая.
Шаблон ищет разделенные запятыми значения в строке.

36. Самостоятельное задание №8

Используя регулярные выражения,
выведите
–
–
–
–
название города,
название улицы,
номер дома, и
номер квартиры
в отдельных столбцах, вырезав их из
столбца address, который содержит полный
адрес.

37. Самостоятельное задание №9

Используя регулярные выражения,
вырежьте из паспортных данных дату
выдачи паспорта.
Даты выдачи паспорта может иметь разные форматы.
PASSPORT
10/05/2007 отд. по р. Западное Дегунино г.Москвы
ОВД Московской области 12.11.2008 КП-77711
10 ОВД МОСКВЫ 10.01.2001
ОВД г.Воронеж 06.05.2009
01.05.09 РОВД г. Домодедово
РОВД Первомайского района г.Чебоксары 06.05.2009
04/01/2005 ОВД района Южное Бутово г.Москвы
выдан 30/01/2005 ОВД района Медведково г.Москвы
IDATE
10.05.2007
12.11.2008
10.01.2001
06.05.2009
01.05.2009
06.05.2009
04.01.2005
30.01.2005

38. Функция regexp_instr()

39. Функция regexp_instr()

Функция regexp_instr() ищет по шаблону и
возвращает первую позицию подстроки.
Если подстрока не найдена, возвращает 0.
Синтаксис:
regexp_instr (исходная_строка, шаблон)
Пример использования:
select
regexp_instr('[email protected]','[[:punct:]]')
from dual
--------5

40. Функция regexp_replace()

41. Функция regexp_replace()

Функция regexp_replace() находит
подстроку по указанному шаблону и заменяет
на заданную строку.
Синтаксис:
regexp_replace (исх_строка, шаблон, строка_замены)
Пример использования:
select regexp_replace(full_name, ' {2,}',' ')
from customers
Данный запрос заменяет все пробелы, которые
повторяются 2 или более раз, на один.

42. Самостоятельное задание №10

Используя регулярные выражения
1. удалите из номеров телефонов все служебные и
пунктуационные символы
2. удалите все символы из номеров телефонов,
которые не являются цифрами.
PHONE_NUMBER DIGITS
8 (903)9730970
89039730970
+79265818729
79265818729
+7(903)2220225
79032220225
+7 905 7132872
79057132872
+7 905 9530568
79059530568

43. Обратные ссылки в регулярных выражениях

44. Обратные ссылки в регулярных выражениях

• Обратные ссылки используются для повторного
использования подстрок
• Подстроки определяются скобками ( ) и нумеруются
числами от 1 и выше слева направо.
• Для обращения к подстрокам обратными ссылками
используется метасимвол \ (обратный слэш)
select regexp_replace ('Фамилия Имя Отчество',
'([^ ]+) ([^ ]+) ([^ ]+)', '\2 \3, \1') from
dual
-----------------Имя Отчество, Фамилия

45. Самостоятельное задание №11

Используя обратные ссылки в регулярных
выражениях:
1. Выведите ФИО клиентов в формате: Имя
Фамилия.
Krysanova Elena Vladimirovna
Elena Krysanova
Kuznetsov Aleksandr Gennadyevich Aleksandr Kuznetsov
Sazonov Alexandr Igorevich
Alexandr Sazonov
2. Выведите ФИО в формате: Фамилия И.О.
Agivetova Rahat Kazakbaevna
Efimova Sveta Nikolaevna
Klimov Igor Vladimirovich
Agivetova R.K.
Efimova S.N.
Klimov I.V.

46. Самостоятельное задание №12

Используя обратные ссылки в регулярных
выражениях:
1. выведите слово из 3 букв в обратном
порядке
cat -> tac
Abc -> cbA

47. Все.