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

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

регулярные выражения

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

 Регуля́рные выраже́ния (англ. 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 шаблона в строку

-- пример использования 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

Чалышев М.М