Использование регулярных выражений REGEXP в ORACLE SQL
регулярные выражения
Согласно Вики
Регуля́рные выраже́ния (англ. regular expressions, сокр. RegExp, RegEx, жарг. регэ́кспы или ре́гексы) это формальный язык поиска и осуществления манипуляций с подстроками в тексте , основанный на использовании метасимволов (символов-джокеров, англ. wildcardcharacters).
По сути это строка-образец (англ. pattern, по-русски её часто называют «шаблоном», «маской»), состоящая из символов и метасимволов и задающая правило поиска.
Для работы с регулярными выражениями в Oracle SQL используются следующие операторы REGEXP_LIKE, REGEXP_REPLACE,REGEXP_SUBSTR, REG_EXPCOUNT, REG_INSTR
Рассмотрим работу каждой из этих команд для наглядности создадим временную таблицу preserved_rows и заполним ее след.данными:
- Фамилия, ДАТА РОЖД, город проживания -- данные предсталвены спложным символьным буфером, с разделитлем ","
CREATE GLOBAL TEMPORARY TABLE regtest ON COMMIT PRESERVE ROWS
AS SELECT 'Зайцев,01111998,Киев' dt FROM dual union
SELECT 'Иванов,01011982,Воронеж' dt FROM dual union SELECT 'Петров,01011988,Москва' dt FROM dual;
REGEXP_LIKE
-- REGEXP_LIKE выбирает из таблицы все строки соответвующие заданному шаболну регулярного выражения REGEXP
-- пример использования REGEXP_LIKE(выражение; 'regexp шаблон') :
-- выберем из таблицы все строчки которые содержат дату рождения в заданном формате REGEXP [0-9]{8}
select * from regtest where regexp_like(dt,'[0-9]{8}')
-- результат
--Зайцев,01111998,Киев
--Иванов,01011982,Воронеж
--Петров,01011988,Москва
-- добавим строку с нестандартным форматом даты
insert into regtest values ('Волков,010A1988,Дмитров');
--выполним запрос повтроно
select * from regtest where regexp_like(dt,'[0-9]{8}')
-- результат
--Зайцев,01111998,Киев
--Иванов,01011982,Воронеж
--Петров,01011988,Москва
-- REGEXP_REPLACE
-- REGEXP_REPLACE заменяет шаболн регулярного выражения REGEXP в строке на заданный строку
-- пример использования REGEXP_REPLACE(выражение; 'regexp шаблон', 'regexp шаблон' или константа) :
-- заменим дату рождения в заданном формате REGEXP [0-9]{8} на выражене mydate
select REGEXP_REPLACE(t.dt,'[0-9]{8}','mydate') rr from regtest t
-результат
Зайцев,mydate,Киев
Иванов,mydate,Воронеж
Петров,mydate,Москва
Волков,010A1988,Дмитров
-- REGEXP_SUBSTR
-- REGEXP_SUBSTR выделяет из строки заданный REGEXP шаблон
-- пример использования REGEXP_REPLACE(выражение; 'regexp шаблон'; вхождение; параметр_сопоставления) :
-- выделим дату рождения в заданном формате REGEXP [0-9]{8} из общей строки
select REGEXP_SUBSTR(t.dt,'[0-9]{8}') rr,t.dt rr from regtest t
результат
01111998 Зайцев,01111998,Киев
01011982 Иванов,01011982,Воронеж
01011988 Петров,01011988,Москва
Волков,010A1988,Дмитров
-- REGEXP_INSTR
-- REGEXP_INSTR определяет номер первого символа вхождения REGEXP шаблона в строку
-- пример использования REGEXP_COUNT(исходная_строка; шаблон; начальная_позиция поиска ;вхождение ;опция_возврата) :
-- оределим кол цифр REGEXP [0-9] в строках таблицы
select REGEXP_instr(t.dt,'[0-9]{8}') rr,t.dt rr from regtest t
-- результат
8 Зайцев,01111998,Киев
8 Иванов,01011982,Воронеж
8 Петров,01011988,Москва
0 Волков,010A1988,Дмитров
-- REGEXP_COUNT
REGEXP_COUNT определяет кол во вхождений REGEXP шаблона в строку
пример использования REGEXP_COUNT(выражение; 'regexp шаблон') :
определим кол цифр REGEXP [0-9] в строках таблицы
select REGEXP_COUNT(t.dt,'[0-9]{1}') rr,t.dt rr from regtest t
-- результат
8 Зайцев,01111998,Киев
8 Иванов,01011982,Воронеж
8 Петров,01011988,Москва
7 Волков,010A1988,Дмитров
Некоторые интересные примеры использования REGEXP
выираем подстроку с разделителями, то есть строка Зайцев,01111998,Киев = Зайцев 01111998 Киев
для примера выберем только фамилию и город
select regexp_substr(t.dt,'[^,]+',1,1) f, regexp_substr(t.dt,'[^,]+',1,3) city from regtest t
результаты
Зайцев Киев
Иванов Воронеж
Петров Москва
Волков Дмитров
воспользовавшись данным методом в сочетании с командой CONNECT by можно преобразовать каджую подстроку с разделителями в строку таблицы
select regexp_substr('Петров,01011988,Москва','[^,]+',1,level) ll
from dual
connect by level <= REGEXP_COUNT('Петров,01011988,Москва',',')+1
Чалышев М.М
