Главная > 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
    ——
    Иванов И.И., Петров Д.Д., Васечкин А.А.

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