Главная > SQL > Регулярные выражения Oracle (regular expression)


Регулярные выражения Oracle (regular expression)


Регулярные выражения произошли из теорий автоматов и формальных языков, поэтому поначалу производят устрашающее впечатление. Однако, их базовые понятия являются простыми и в то же время мощными.
Начиная с версии Oracle 10g регулярные выражения можно использовать напрямую в SQL запросах.

Шаблон регулярных выражений выражается в виде строки, содержащей следующие конструкции:

  • Литеральные символы. Фактические символы, которые следует искать (Например, шаблон xyz соответствует только вхождению «xyz»)
  • Метасимволы. Операции, определяющие алгоритмы, которые должны применяться во время поиска (Например, шаблон ^xyz соответсвует только строке, начинающейся с «xyz» — другие вхождения не учитываются)

Оператор REGEXP_LIKE

REGEXP_LIKE(исходная_строка, шаблон[, параметр_сопоставления])

исходная_строка поддерживает символьные типы данных (CHAR, VARCHAR2, CLOB, NCHAR, NVARCHAR2 и NCLOB, но не LONG)
шаблон это другое название регулярного выражения
параметр_сопоставления позволяет использовать дополнительные параметры, такие как символ перехода на новую строку, многострочное форматирование и обеспечение управления учетом регистра

Используется подобно оператору like в части where или же при определении ограничения на таблицу (constraint) .

Пример использования регулярных выражений:

Функция REGEXP_INSTR

REGEXP_INSTR(исходная_строка, шаблон[, начальная_позиция [, вхождение [, опция_возврата [, параметр_сопоставления ] ] ] ] )

исходная_строка поддерживает символьные типы данных (CHAR, VARCHAR2, CLOB, NCHAR, NVARCHAR2 и NCLOB, но не LONG)
шаблон регулярное выражение
начальная_позиция позиция, с которой должен начинаться поиск
вхождение по умолчанию имеет значение 1, если пользователь не укажет поиск последовательных вхождений
опция_возврата значение по умолчанию 0, тогда возвратится начальная позиция шаблона; при значении 1 возвращается позиция символа, следующего за шаблоном
параметр_сопоставления позволяет использовать дополнительные параметры, такие как символ перехода на новую строку, многострочное форматирование и обеспечение управления учетом регистра

Функция возвращает позицию символа, находящегосяв начале или конце соответствия для шаблона, так же как и ее аналог instr.

В отличие от instr, функция regexp_instr работает с начала строки и двигается вперед в поисках шаблона. Она не может начать с конца строки и перемещаться в обратном направлении.

Функция REGEXP_SUBSTR

REGEXP_SUBSTR(исходная_строка, шаблон[, позиция [, вхождение [,параметр_сопоставления]]])

исходная_строка поддерживает символьные типы данных (CHAR, VARCHAR2, CLOB, NCHAR, NVARCHAR2 и NCLOB, но не LONG)
шаблон регулярное выражение
позиция позиция, с которой необходимо начинать поиск
вхождение по умолчанию имеет значение 1
параметр_сопоставления позволяет использовать дополнительные параметры, такие как символ перехода на новую строку, многострочное форматирование и обеспечение управления учетом регистра


Функция REGEXP_SUBSTR возвращает подстроку, которая соответствует шаблону.

Пример использования регулярных выражений:

Функция REGEXP_REPLACE

REGEXP_REPLACE(исходная_строка, шаблон [, строка_замены [, позиция[,вхождение, [параметр_сопоставления]]]])

исходная_строка поддерживает символьные типы данных (CHAR, VARCHAR2, CLOB, NCHAR, NVARCHAR2 и NCLOB, но не LONG)
шаблон регулярное выражение
шаблон замены текст для замены каждого вхождения
позиция позиция, с которой необходимо начинать поиск
вхождение по умолчанию имеет значение 1
параметр_сопоставления позволяет использовать дополнительные параметры, такие как символ перехода на новую строку, многострочное форматирование и обеспечение управления учетом регистра

REGEXP_REPLACE возвращает измененную входную строку, в которой все вхождения шаблона заменены значением, переданным в параметре строка_замены.

Пример использования регулярных выражений:

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

Таблица 1: Метасимволы привязки

Метасимвол Описание Пример
^ Привязать выражение к началу строки «^привет» соответствует «привет, как дела», но не «как дела, привет»
$ Привязать выражение к концу строки «привет$» соответсвует «как дела, привет», но не «привет, как дела»

Таблица 2: Квантификаторы и операторы повтора

Квантификатор Описание Пример
* Встречается 0 и более раз REGEXP_REPLACE(str, ’11*’, ‘1’)
Результат:
test11 => test1
11123345 => 123345
? Встречается 0 или 1 раз
+ Встречается 1 и более раз REGEXP_LIKE(str,’5+’)
Результат:
test11 => false
11123345 => true
{m} Встречается ровно m раз REGEXP_LIKE(str,’3{2}’)
Результат:
test11 => false
11123345 => true
{m,} Встречается по крайней мере m раз
{m, n} Встречается по крайней мере m раз, но не более n раз

Таблица 3: Предопределенные символьные классы POSIX

Класс символов Описание
. Любой символ
[:alpha:] Буквы
[:lower:] Буквы в нижнем регистре
[:upper:] Буквы в верхнем регистре
[:digit:] Цифры
[:alnum:] Буквы и цифры
[:space:] Пробелы (не печатаемые символы), такие как перевод каретки, новая строка, вертикальная табуляция и подача страницы
[:punct:] Знаки препинания
[:cntrl:] Управляющие символы (не печатаемые)
[:print:] Печатаемые символы

Таблица 4: Альтернативное сопоставление и группировка выражений

Метасимвол Описание
| Альтернатива Разделяет альтернативные варианты, часто используется с оператором группировки ()
( ) Группа Группирует подвыражения для альтернативы, квантификатора или ссылочности
[char] Список символов Обозначает список символов; большинство метасимволов в списке символов представляют собой литеры, за исключением символьных классов и метасимволов ^ и —
[^char] Список символов Список символов, которые не должны присутствовать в строке

Таблица 5: Метасимвол ссылки

Метасимвол Описание
\digit Обратная косая черта

За ней следует цифра от 1 до 9, обратная косая черта связана с предыдущим сопоставлением с соответствующим номером заключенного в скобки подвыражения.

(Заметьте: Обратная косая черта может иметь другое значение в регулярном выражении; в зависимости от контекста она может означать также символ Escape

Более полную информацию можно прочитать здесь Using Regular Expressions in Oracle Database

Похожие записи:

  1. Али
    23 Апрель 2012 в 21:48 | #1

    Вывести три поля : наименование (naimen ), дату (GR0) , дату которая написали в наименование .
    Использовать регулярные выражения . Таблица atfm.uch_vse.

  2. 24 Апрель 2012 в 10:13 | #2

    @Али
    А в чем вопрос заключается?

  3. Федя
    24 Апрель 2012 в 11:56 | #3

    @rudev
    Наверное он хочет чтобы ему помогли сформировать SQL запрос соответствующий..

  4. Andrey
    2 Май 2012 в 11:02 | #4

    Что то вроде:

    select naimen,
           GR0,
           regexp_substr(naimen, '[[:digit:]]{2}[[:punct:]][[:digit:]]{2}[[:punct:]][[:digit:]]{2,4}') as "DATE_FROM_NAIMEN"
      from atfm.uch_vse
    

    ???

  5. Mary
    4 Март 2014 в 21:14 | #5

    Подскажите, как оптимально сделать запрос с паспортами российскими нового образца. Если серия иногда указывается с номером. Вывести и серию, и номер, заранее спасибо

  6. Mary
    5 Март 2014 в 21:28 | #6

    Андрей, в подскажите, с запросом выше, если не затруднит
    @Andrey

  7. Arty
    9 Апрель 2014 в 17:19 | #7

    Народ, помогите пожалуйста !!
    Нужно сделать вот такой запрос:
    Выявить наименования содержащие лишние спец.символы (например, повторяющиеся пробелы, \, *, ^, ?, [, ], {, }, $, ., +, , (, ,))

  8. Arty
    9 Апрель 2014 в 17:20 | #8

    Нужно сделать через regexp_like

  9. Gennadij
    22 Апрель 2014 в 08:53 | #9

    Кто знает, как в Оракле 11 при помощи регулярного выражения разбить предложение на слова , чтобы при этом пустые слова тоже учитывались. Слово в данном случае это ноль или больше символов между двумя разделителями, в качестве которых также служат начало и конец предложения. Без учета пустых символов легко regexp_substr(str, ‘[^;]+’,1,n) — n-е слово. Если вместо + поставить * то тогда и разделитель каждый тоже является отдельным словом

  10. Илья
    23 Июнь 2014 в 18:25 | #10

    select level, replace(regexp_substr(‘ff,rrr,,y’||’,’, ‘[^,]*,’,1,level),’,’) from dual
    connect by regexp_substr(‘ff,rrr,,y’||’,’, ‘[^,]*,’,1,level) is not null

  11. Илья
    23 Июнь 2014 в 18:56 | #11

    или так
    select level, replace(regexp_substr(‘ff,rr;rrr,,y’, ‘[^,]*(,|$)’,1,level),’,’) from dual
    connect by regexp_substr(‘ff,rr;rrr,,y’, ‘[^,]*(,|$)’,1,level) is not null

  12. Кокос
    2 Февраль 2015 в 19:16 | #12

    Да, если б еще оракл «lookahead/lookbehind» (?=)(?<=) поддерживал, чтоб не надо было префикс/постфикс из результата выковыривать отдельно…

  13. Даша
    26 Апрель 2016 в 14:52 | #13

    Добрый день, уважаемые программисты!
    Подскажите, пожалуйста, как реализовать фильтр на символы?
    Допустимые значения в имени: [ 0-9 a-z A-Z — + (пробел) , _ . «], а лишние символы удалить или заменить.

    Буду благодарна за ответ!

  14. Даша
    26 Апрель 2016 в 15:36 | #14

    Нашла решение!

    FUNCTION REGREX_STR(STR IN VARCHAR2) RETURN RETURN VARCHAR2 IS
    RES VARCHAR(100);
    BEGIN
    select regexp_replace(tt.s, ‘[‘||tt.new_str||’^]’) into RES
    from
    ( select str as s, trim(regexp_replace(t.str, ‘[[:alnum:][:space:]+-_.,»]’)) as new_str
    from (
    select STR as str from dual
    ) t
    where regexp_like(t.str, ‘[[:alnum:][:space:]+-_.,»]’)
    ) tt
    where regexp_like(tt.s, ‘[‘||tt.new_str||’^]’);
    RETURN RES;
    END;

  15. imp
    26 Май 2016 в 07:12 | #15

    Подскажите. как можно из строки убрать фамилию которая повторяется несколько раз?
    Для примера: Иванов И.И., Иванов И.И., Петров Д.Д., Васечкин А.А.
    Выводить как: Иванов И.И., Петров Д.Д., Васечкин А.А.

  16. Игорь
    26 Май 2016 в 09:28 | #16
    with t as
     (select 'Иванов И.И., Иванов И.И., Петров Д.Д., Васечкин А.А.' as str
        from dual)
    select distinct trim(regexp_substr(str,
                                       '[^,]+',
                                       1,
                                       level)) as uniq_name
      from t
    connect by regexp_instr(str,
                            '[^,]+',
                            1,
                            level) > 0

    Результат:
    UNIQ_NAME
    —————
    Петров Д.Д.
    Васечкин А.А.
    Иванов И.И.

  17. Vano
    30 Март 2017 в 12:02 | #17

    imp, еще простой вариант для вывода одной строкой:
    select REGEXP_REPLACE(‘Иванов И.И., Иванов И.И., Петров Д.Д., Васечкин А.А.’,'(([^,]+), ?)\1′,’\1′,1,1,’i’) as str
    from dual

    Результат:
    STR
    ——
    Иванов И.И., Петров Д.Д., Васечкин А.А.

  18. Vladimir
    18 Июль 2017 в 06:28 | #18

    Доброго времени суток.
    Подскажите, как с помощью regexp_replace оставить из строки «АПКН@1112» только АП пробую

    select 
       regexp_replace('АПКН@1112', '[^АП]')
    from dual

    Результат: АП
    но если строка «АКН@1112» возвращает А, мне необходимо, что бы АП воспринимал как группу пробовал ‘[^(АП)]’ не работает.

  19. 21 Июль 2017 в 13:34 | #19

    @Vladimir
    А точно нужен именно regexp_replace ?

    Если нужно, чтобы строка начиналась на АП, то проще искать через like ‘АП%’

    Можно конечно еще через regexp_substr:

    SQL>
     with t as (
      select 'АКН@11АП12' as str from dual
      union all
      select 'АКН@1112' from dual
      union all
      select 'АПКН@1112' from dual
     )
     select str, regexp_substr(str, '^АП') as res from t;
     >
    STR        RES
    ---------- ----------
    АКН@11АП12 
    АКН@1112   
    АПКН@1112  АП

    А если просто наличие АП, то :

    SQL> with t as (
      select 'АКН@11АП12' as str from dual
      union all
      select 'АКН@1112' from dual
      union all
      select 'АПКН@1112' from dual
      )
      select str, regexp_substr(str, 'АП') as res from t;
      >
    STR        RES
    ---------- ----------
    АКН@11АП12 АП
    АКН@1112   
    АПКН@1112  АП
    

  20. Nasty
    21 Июль 2017 в 19:29 | #20

    Может кто подскажет, нужно чтобы выводились все значения расположенные после двоеточия, но при этом, если в теге двоеточия нет, то выводилось его значение. К примеру, для строки «ffgfg:45454|fdfd:511|00|mck:9» должно выводится 45454|511|00|9, а у меня получается 45454|511|9.

    Написала вот что, все выводит почти верно, кроме тех значений, где после | не было двоеточия.. Т.е. в моем примере он Не выодит 00, а должен!

    declare
    p_element_path varchar2(4000);
    p_element_path_new varchar2(4000);
    cnt number := 0;
    begin
    p_element_path := ‘ffgfg:45454|fdfd:511|00|mck:9’;
    for i in (SELECT REGEXP_SUBSTR(p_element_path, ‘[^|;]+’, REGEXP_INSTR(p_element_path, ‘[^:;]+’,1, LEVEL, 0), 1) a1
    FROM DUAL
    CONNECT BY REGEXP_INSTR(p_element_path, ‘[^:;]+’, 1, LEVEL) > 0) loop
    cnt:= cnt+1;
    if cnt >1 then
    p_element_path_new:= p_element_path_new||’|’||i.a1;
    end if;
    end loop;
    end;

  21. 26 Июль 2017 в 13:06 | #21

    @Nasty
    Обработку пустого значения, можно сделать через nvl

    with t as 
    (select 1 n, 'ffgfg:45454|fdfd:511|00|mck:9' str from dual
     union all
     select 2, 'ffgfg:45454|fdfd:511|asds:02|mck:9' str from dual
     union all
     select 3, ':45454|fdfd:511|asds:03|:mck:9' str from dual 
    )
    select n, str, lvl, str2, nvl(REGEXP_substr(str2,'[^:]+',1,2),str2) as res
    from (
    select n, t.str, level lvl, ltrim(regexp_substr(str, '[^|]+', 1, level),':') str2
    from t
    CONNECT BY instr(str, '|', 1, level - 1) > 0 and prior n = n and prior sys_guid() is not null
    ) d
    >
             N STR                                         LVL STR2                             RES
    ---------- ---------------------------------- ---- ----------------------- ----------------------------
             1 ffgfg:45454|fdfd:511|00|mck:9               1 ffgfg:45454                        45454
             1 ffgfg:45454|fdfd:511|00|mck:9               2 fdfd:511                           511
             1 ffgfg:45454|fdfd:511|00|mck:9               3 00                                 00
             1 ffgfg:45454|fdfd:511|00|mck:9               4 mck:9                              9
             2 ffgfg:45454|fdfd:511|asds:02|mck:9          1 ffgfg:45454                        45454
             2 ffgfg:45454|fdfd:511|asds:02|mck:9          2 fdfd:511                           511
             2 ffgfg:45454|fdfd:511|asds:02|mck:9          3 asds:02                            02
             2 ffgfg:45454|fdfd:511|asds:02|mck:9          4 mck:9                              9
             3 :45454|fdfd:511|asds:03|:mck:9              1 45454                              45454
             3 :45454|fdfd:511|asds:03|:mck:9              2 fdfd:511                           511
             3 :45454|fdfd:511|asds:03|:mck:9              3 asds:03                            03
             3 :45454|fdfd:511|asds:03|:mck:9              4 mck:9                              9
    12 rows selected
    

  22. Аноним
    2 Июль 2018 в 16:00 | #22

    @Nasty

    with t1 (str,lv) as
    (select 'start', 1 lv from dual
    union all
    select regexp_substr('ffgfg:45454|fdfd:511|00|mck:9','\d+(?=||$)',1,lv) str, lv+1 lv
    from t1
    where t1.str is not null)
    select listagg(str,'|') within group (order by lv) from t1 where regexp_like(str,'\d+')
  23. Андрей
    31 Январь 2019 в 18:42 | #23

    Коллеги, а как быть, если нужно использовать управляющие символы, например, кавычку или скобку? Как например, найти слово, которое заключено в одинарные кавычки или в скобки?

  24. Аноним
    7 Май 2019 в 21:35 | #24

    Так не пойдет?
    with t as (

    select ‘(img src=»http://apps-oracle.ru/logo2.png» alt)=»»/>DDD’ str from dual

    )

    select regexp_substr(str, ‘\(.*\)’) as res, chr(37) ypr, ascii(‘(‘) psk, ascii(‘)’) lsk,

    chr(40) s_40, chr(41) s_41

    from t;

  25. АНОН
    21 Октябрь 2019 в 08:11 | #25

    ПОМОГИТЕПОЖАЛУЙСТА!
    Создать запрос для вывода фамилий служащих и их зарплат, обозначенных звездочками (каждая звездочка обозначает 100 долларов).

  26. Аноним
    14 Январь 2020 в 15:53 | #26

    Добрый день! Подскажите пожалуйста, какой фильтр(выражение)правильно поставить чтобы в столбце можно было отфильтровать строки «начиная с»

  27. KoreanNoName
    16 Январь 2020 в 16:34 | #27

    @Аноним
    where Столбец like ‘начиная с%’

  28. KoreanNoName
    16 Январь 2020 в 16:35 | #28

    @АНОН
    length(поле со звездочками)*100

  29. sdan
    4 Август 2020 в 12:39 | #29

    Добрый день!
    Имеется следующая пример в Oracle, хотелось бы, что он заменил | на ; везде, кроме где есть предшествующий символ /

    select regexp_replace(’16|2/|3 |7 |8′, ‘(?<!\/)\|', ';') from dual;

    Регулярку проверил для питона, PHP она корректно работает, а для Oracle не понимаю, как сделать альтернативу.

  30. Аноним
    31 Август 2020 в 11:47 | #30

    Коллеги, подскажите как заменить вхождение «Акционерное общество» на «АО» без учета регистра?
    В функции regexp_replace ставлю в конце параметр ‘i’, но он все равно не меняет. При этом если «Акционерное общество» заменить просто любым одним символом, то меняет

  31. Андрей
    18 Февраль 2021 в 12:22 | #31

    Есть проблема, нужно выдернуть все номера телефонов из поля адреса, все значения хранятся через запятую, в одном поле адреса, вместе с адресом, телефона может не быть, может быть 1, может быть 2. Никак не могу найти решение, пробовал через REGEXP_SUBSTR, вероятно в силу того, что только начал с ним разбираться получаю 1 первый номер и все, как можно сделать, написал так:
    select
    address,
    REGEXP_SUBSTR(address,’\D{5}?\(?\d{3}\)?[[:space:]\.\-]?\d{3}[[:space:]\.\-]?\d{4}’,1,1) tel
    from kadrobject
    where kadrid=’2004′

    Пример данных:
    Москва, Генерала Белова, д. 100, кв. 120, дом. 75555555555, моб. 7 955 555-5555

  1. Пока что нет уведомлений.