REGEXP_INSTR
📖 Описание
Функция Oracle/PLSQL REGEXP_INSTR расширяет INSTR и возвращает позицию совпадения регулярного выражения. Позиции отсчитываются в символах, начиная с 1; при отсутствии совпадения возвращается 0.
🧩 Синтаксис Oracle 19c
REGEXP_INSTR(source_string, pattern
[, position [, occurrence [, return_option [, match_param [, subexpr]]]]])
⚙️ Параметры и аргументы
source_string — исходная строка; position и occurrence — положительные начальная позиция и номер совпадения, оба по умолчанию равны 1.
return_option = 0 возвращает начало совпадения (по умолчанию), 1 — позицию сразу после всего совпадения. subexpr от 0 до 9 выбирает всё совпадение или конкретную группу; по умолчанию 0.
Группы нумеруются по открывающим скобкам. Для отсутствующей группы результат равен 0, для subexpr = NULL — 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_INSTR('Open workshop', 'o', 1, 1, 0, 'c') FROM dual;
-- Результат: 7
SELECT REGEXP_INSTR('Open workshop', 'o', 1, 1, 0, 'i') FROM dual;
-- Результат: 1
WITH workshop_titles (workshop_id, title) AS (
SELECT 1, 'Open workshop' FROM dual UNION ALL
SELECT 2, 'Wood lab' FROM dual UNION ALL
SELECT 3, 'Clay class' FROM dual
)
SELECT workshop_id, title,
REGEXP_INSTR(title, 'o', 1, 1, 0, 'i') AS first_o
FROM workshop_titles
ORDER BY workshop_id;
| WORKSHOP_ID | TITLE | FIRST_O |
|---|---|---|
| 1 | Open workshop | 1 |
| 2 | Wood lab | 2 |
| 3 | Clay class | 0 |
Несколько символов и начальная позиция
SELECT REGEXP_INSTR('Red box, blue box', 'box', 1, 1, 0, 'c') FROM dual;
-- Результат: 5
SELECT REGEXP_INSTR('Red box, blue box', 'box', 6, 1, 0, 'c') FROM dual;
-- Результат: 15
При поиске с позиции 6 первое слово box уже не может совпасть целиком, поэтому находится второе. Результат 15 относится к исходной строке, а не к началу поиска.
WITH workshop_titles (workshop_id, title) AS (
SELECT 1, 'Open workshop' FROM dual UNION ALL
SELECT 2, 'Wood lab' FROM dual UNION ALL
SELECT 3, 'Clay class' FROM dual
)
SELECT workshop_id,
REGEXP_INSTR(title, 'lab', 1, 1, 0, 'i') AS lab_position
FROM workshop_titles
ORDER BY workshop_id;
| WORKSHOP_ID | LAB_POSITION |
|---|---|
| 1 | 0 |
| 2 | 6 |
| 3 | 0 |
Несколько альтернатив
SELECT REGEXP_INSTR('Olive', 'a|e|i|o|u', 1, 1, 0, 'c') FROM dual;
-- Результат: 3
SELECT REGEXP_INSTR('Olive', 'a|e|i|o|u', 1, 1, 0, 'i') FROM dual;
-- Результат: 1
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_INSTR(title, 'a|e|i|o|u', 1, 1, 0, 'i') AS first_vowel
FROM studio_names
ORDER BY studio_id;
| STUDIO_ID | TITLE | FIRST_VOWEL |
|---|---|---|
| 1 | Olive | 1 |
| 2 | Maple | 2 |
| 3 | Cedar | 2 |
Номер совпадения
SELECT REGEXP_INSTR('Olive', 'a|e|i|o|u', 1, 1, 0, 'i') FROM dual;
-- Результат: 1
SELECT REGEXP_INSTR('Olive', 'a|e|i|o|u', 1, 2, 0, 'i') FROM dual;
-- Результат: 3
SELECT REGEXP_INSTR('Olive', 'a|e|i|o|u', 1, 3, 0, 'i') FROM dual;
-- Результат: 5
Позиция после совпадения
SELECT REGEXP_INSTR('Red box, blue box', 'box', 1, 1, 0, 'c') FROM dual;
-- Результат: 5
SELECT REGEXP_INSTR('Red box, blue box', 'box', 1, 1, 1, 'c') FROM dual;
-- Результат: 8
Совпадение box занимает позиции 5–7, поэтому return_option = 1 возвращает 8.
См. документацию Oracle: REGEXP_INSTR.
См. документацию Oracle: операторы регулярных выражений.
Подробности якорей и классов: Oracle Database Development Guide.