Joomla 3.1 Templates by FatCow Coupon

Использование регулярных выражений REGEXP в ORACLE SQL

Категория: Блог Опубликовано 23.05.2017

Согласно Вики

Регулярные выражения (англ. regular expressions, сокр. RegExp, RegEx, жарг. регэкспы или регексы) это формальный язык поиска и осуществления манипуляций с подстроками в тексте , основанный на использовании метасимволов (символов-джокеров, англ. wildcard characters).

По сути это строка-образец (англ. 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 шаблона в строку

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
 

 результаты:111

Зайцев Киев
Иванов Воронеж
Петров Москва
Волков Дмитров

воспользовавшись данным методом в сочетании с командой CONNECT by можно преобразовать каждую подстроку с разделителями в строку таблицы

 select  regexp_substr('Петров,01011988,Москва','[^,]+',1,level) ll
       from dual
     connect by level <=  REGEXP_COUNT('Петров,01011988,Москва',',')+1
 

 

 

Просмотров: 1172