REGEXP_SUBSTR

📖 Описание

Функция Oracle/PLSQL REGEXP_SUBSTR расширяет возможности SUBSTR: она извлекает подстроку, найденную по регулярному выражению.


🧩 Синтаксис Oracle 19c

REGEXP_SUBSTR(source_string, pattern
  [, position [, occurrence [, match_param [, subexpr]]]])

⚙️ Параметры и аргументы

source_string — строка CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB или NCLOB. position — положительная начальная позиция, по умолчанию 1; occurrence — положительный номер совпадения, по умолчанию 1. Значение 0 для occurrence здесь недопустимо.

subexpr выбирает группу от 0 до 9: 0 означает всё совпадение, 1–9 — группу по порядку открывающей скобки. При отсутствии совпадения возвращается NULL; поиск следующего совпадения начинается после конца предыдущего.

Шаблон pattern

Регулярное выражение ограничено 512 байтами. Основные операторы:

Оператор Назначение
^ Начало строки; с m — начало каждой строки текста
$ Конец строки; с m — конец каждой строки текста
* Ноль или более повторений предыдущего элемента
+ Одно или более повторений
? Ноль или одно повторение
. Любой символ; перевод строки включается только с параметром n
a|b Альтернатива: a или b
[abc] Один символ из набора
[^abc] Один символ вне набора
(...) Группа (подвыражение)
{m}, {m,}, {m,n} Ровно m, не менее m, от m до n повторений
\1 … \9 Обратная ссылка на ранее захваченную группу
[[.ch.]] Элемент сортировки, определённый локалью
[[:alpha:]] Класс символов; здесь буквы
[[=a=]] Класс эквивалентности, зависящий от локали
\d / \D Цифра / не цифра
\w / \W Буква, цифра или подчёркивание / другой символ
\s / \S Пробельный / непробельный символ
\A Начало всего текста независимо от m
\Z Конец всего текста, также позиция перед завершающим переводом строки
*?, +?, ?? Нежадные варианты: минимальное число повторений, позволяющее остальному шаблону совпасть
{m}?, {m,}?, {m,n}? Нежадные варианты ограниченного повторения

Параметр match_param

Значение Поведение
c Учитывать регистр и диакритические знаки
i Не учитывать регистр
n Разрешить точке совпадать с переводом строки
m Считать ^ и $ границами каждой строки текста
x Игнорировать пробельные символы в шаблоне

При конфликте c и i действует последний символ. Без явного c или i чувствительность определяется правилами сравнения строк (collation), поэтому в примерах она указана явно.


💻 Примеры

Слова и пробелы

SELECT '[' || REGEXP_SUBSTR('Studio offers pottery classes today', '\S+\s', 1, 1, 'c') || ']' FROM dual;
-- Результат: [Studio ]

SELECT REGEXP_SUBSTR('Studio offers pottery classes today', '\S+', 1, 1, 'c') FROM dual;
-- Результат: Studio

SELECT '[' || REGEXP_SUBSTR('Studio offers pottery classes today', '\S+\s', 1, 2, 'c') || ']' FROM dual;
-- Результат: [offers ]

SELECT '[' || REGEXP_SUBSTR('Studio offers pottery classes today', '\S+\s', 1, 4, 'c') || ']' FROM dual;
-- Результат: [classes ]

\S+ требует хотя бы один непробельный символ, а завершающее \s включает пробел после слова. Для последнего слова без конечного пробела используйте вариант без \s.

Цифры

SELECT REGEXP_SUBSTR('Boxes: 3, 7, 24', '\d', 1, 1, 'c') FROM dual;
-- Результат: 3

SELECT REGEXP_SUBSTR('Boxes: 3, 7, 24', '\d{2}', 1, 1, 'c') FROM dual;
-- Результат: 24
WITH studio_rooms (room_id, label) AS (
  SELECT 1, 'Room 24, shelf 7' FROM dual UNION ALL
  SELECT 2, 'Room 8, shelf 31' FROM dual
)
SELECT room_id, REGEXP_SUBSTR(label, '\d{2}', 1, 1, 'c') AS digit_pair
FROM studio_rooms
ORDER BY room_id;
ROOM_ID DIGIT_PAIR
1 24
2 31

Шаблон \d{2} ищет две последовательные цифры; без дополнительных границ он также может найти пару внутри более длинного числа.

Альтернативы и регистр

SELECT REGEXP_SUBSTR('Olive', 'a|e|i|o|u', 1, 1, 'c') FROM dual;
-- Результат: i

SELECT REGEXP_SUBSTR('Olive', 'a|e|i|o|u', 1, 1, 'i') FROM dual;
-- Результат: O
WITH studio_names (studio_id, title) AS (
  SELECT 1, 'Olive' FROM dual UNION ALL
  SELECT 2, 'Maple' FROM dual UNION ALL
  SELECT 3, 'Cedar' FROM dual
)
SELECT studio_id, title,
       REGEXP_SUBSTR(title, 'a|e|i|o|u', 1, 1, 'i') AS first_vowel
FROM studio_names
ORDER BY studio_id;
STUDIO_ID TITLE FIRST_VOWEL
1 Olive O
2 Maple a
3 Cedar e

Номер совпадения

SELECT REGEXP_SUBSTR('Olive', 'a|e|i|o|u', 1, 1, 'i') FROM dual;
-- Результат: O

SELECT REGEXP_SUBSTR('Olive', 'a|e|i|o|u', 1, 2, 'i') FROM dual;
-- Результат: i

SELECT REGEXP_SUBSTR('Olive', 'a|e|i|o|u', 1, 3, 'i') FROM dual;
-- Результат: e

Параметр occurrence выбирает первое, второе или третье совпадение целого шаблона; он не является номером группы subexpr.

См. документацию Oracle: REGEXP_SUBSTR.

См. документацию Oracle: операторы регулярных выражений.

Подробности якорей и классов: Oracle Database Development Guide.