REGEXP_INSTR ФУНКЦИЯ — Oracle PL/SQL •MySQL •MariaDB •SQL Server •SQLite

В этом учебном пособии вы узнаете, как использовать функцию REGEXP_INSTR Oracle/PLSQL с синтаксисом и примерами.

Описание

Функция Oracle/PLSQL REGEXP_INSTR является расширением функции INSTR. Она возвращает местоположение шаблона регулярного выражения в строке. Эта функция, представленная в Oracle 10g, позволит вам найти подстроку в строке, используя сопоставление шаблонов регулярных выражений.

Синтаксис

Синтаксис функции Oracle/PLSQL REGEXP_INSTR:

REGEXP_INSTR( string, pattern [, start_position [, nth_appearance [, return_option [, match_parameter [, sub_expression ] ] ] ] ] )

Параметры или аргументы

string
Строка для поиска. Строкой могут быть CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB или NCLOB.

pattern

Шаблон. Регулярное выражение для сопоставления. Это может быть комбинацией следующих значений:

Значение	Описание
^	Соответствует началу строки. При использовании match_parameter с m, соответствует началу строки в любом месте в пределах выражения.
$	Соответствует концу строки. При использовании match_parameter с m, соответствует концу строки в любом месте в пределах выражения.
*	Соответствует нолю или более вхождений.
+	Соответствует одному или более вхождений.
?	Соответствует нолю или одному вхождению.
.	Соответствует любому символу, кроме NULL.
\|	Используется как "OR", чтобы указать более одной альтернативы.
[ ]	Используется для указания списка совпадений, где вы пытаетесь соответствовать любому из символов в списке.
[^ ]	Используется для указания списка nonmatching, где вы пытаетесь соответствовать любому символу, за исключением тех кто в списке.
( )	Используется для групповых выражений в качестве подвыражений.
{m}	Соответствует m раз.
{m,}	Соответствие как минимум m раз.
{m,n}	Соответствие как минимум m раз, но не более n раз.
\n	n представляет собой число от 1 до 9. Соответствует n-му подвыражению находящемуся в ( ) перед \n.
[..]	Соответствует одному сопоставлению элемента, который может быть более одного символа.
[::]	Соответствует классу символов.
[==]	Соответствует классу эквивалентности
\d	Соответствует цифровому символу.
\D	Соответствует не цифровому символу.
\w	Соответствует текстовому символу.
\W	Соответствует не текстовому символу.
\s	Соответствует символу пробел.
\S	Соответствует не символу пробел.
\A	Соответствует началу строки или соответствует концу строки перед символом новой строки.
\Z	Соответствует концу строки.
*?	Соответствует предыдущему шаблону ноль или более вхождений.
+?	Соответствует предыдущему шаблону один или более вхождений.
??	Соответствует предыдущему шаблону ноль или одному вхождению.
{n}?	Соответствует предыдущему шаблону n раз.
{n,}?	Соответствует предыдущему шаблону, по меньшей мере n раз.
{n,m}?	Соответствует предыдущему шаблону, по меньшей мере n раз, но не более m раз.

start_position
Необязательный. Это позиция в строке, откуда начнется поиск. Если этот параметр опущен, по умолчанию он равен 1, который является первой позицией в строке.
nth_appearance
Необязательный. Это n-ое вхождение шаблона в строке. Если этот параметр опущен, по умолчанию он равен 1, который является первым вхождением шаблона в строке.
return_option
Необязательный. Если return_option обозначен 0, то возвращается позиция первого символа входящего в шаблон. Если return_option обозначен 1, то возвращается позиция после символа входящего в шаблон. Если этот параметр опущен, он по умолчанию равен 0.

match_parameter
Необязательный. Это позволяет изменять поведение соответствия для условия REGEXP_INSTR. Это может быть комбинацией следующих значений:

Значение	Описание
'c'	Выполняет чувствительное к регистру согласование.
'i'	Выполняет не чувствительное к регистру согласование.
'n'	Позволяет период символа (.) для соответствия символа новой строки. По умолчанию, период метасимволы.
'm'	Выражение допускает, что есть несколько строк, где ^ это начало строки, а $ это конец строки, независимо от позиции этих символов в выражении. По умолчанию предполагается, что выражение в одной строке.
'x'	Игнорируются символы пробелов. По умолчанию, символы пробелов совпадают, как и любой другой символ.

subexpression
Необязательный. Используется, когда шаблон имеет подвыражения, и вы хотите указать, какое подвыражение в шаблоне является целью. Это целочисленное значение от 0 до 9, указывающее, что подвыражение соответствует шаблону.

Функция REGEXP_INSTR возвращает числовое значение.
Если функция REGEXP_INSTR не обнаруживает никакого соответствия шаблону, она вернет 0.

Примечание

Если в параметре match_parameter имеются конфликтующие значения, функция REGEXP_INSTR будет использовать последнее значение.
См. Также функцию INSTR.

Применение

Функцию REGEXP_INSTR можно использовать в следующих версиях Oracle / PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g

Пример совпадения по единичному символу

Начнем с рассмотрения простейшего случая. Давайте найдем позицию первого символа 'o' в строке.
Например:

SELECT REGEXP_INSTR ('Oracle Cloud Infrastructure', 'o') FROM dual;

1	SELECT REGEXP_INSTR ('Oracle Cloud Infrastructure', 'o') FROM dual;

Этот пример вернет 10, потому что он выполняет поиск по регистру 'o' с учетом регистра. Поэтому он пропускает символы 'O' и находит первую 'o' в 10-й позиции.

Если мы захотим включить в результат как маленькую 'o', так и большую 'O' и выполнить поиск без учета регистра, то изменим наш запрос следующим образом:

SELECT REGEXP_INSTR ('Oracle Cloud Infrastructure', 'o', 1, 1, 0, 'i') FROM dual;

1	SELECT REGEXP_INSTR ('Oracle Cloud Infrastructure', 'o', 1, 1, 0, 'i') FROM dual;

Теперь, поскольку мы предоставили start_position = 1, nth_appearance = 1, return_option = 0 и match_parameter = 'i', запрос вернет 1 в качестве результата. На этот раз функция будет искать значения 'o' и 'O' и вернет первое вхождение.

Если бы мы захотели найти первое вхождение символа 'o' в столбце, то мы могли бы попробовать что-то вроде этого (без учета регистра):

SELECT REGEXP_INSTR (last_name, 'o', 1, 1, 0, 'i') AS First_Occurrence FROM contacts;

1	SELECT REGEXP_INSTR (last_name, 'o', 1, 1, 0, 'i') AS First_Occurrence FROM contacts;

Это вернет первое вхождение значений 'o' или 'O' в поле last_name из таблицы contacts.

Пример совпадения нескольких символов

Рассмотрим, как мы будем использовать функцию REGEXP_INSTR для соответствия многосимвольной схеме.
Например:

SELECT REGEXP_INSTR ('The example shows how to use the REGEXP_INSTR function', 'ow', 1, 1, 0, 'i') FROM dual;
--Результат: 15

1 2	SELECT REGEXP_INSTR ('The example shows how to use the REGEXP_INSTR function', 'ow', 1, 1, 0, 'i') FROM dual; --Результат: 15

В этом примере будет возвращено первое вхождение 'ow' в строке. Оно будет соответствовать 'ow' в слове shows.

Мы могли бы изменить начальную позицию поиска, чтобы выполнить поиск, начиная с середины строки.
Например:

SELECT REGEXP_INSTR ('The example shows how to use the REGEXP_INSTR function', 'ow', 16, 1, 0, 'i') FROM dual;
--Результат: 20

1 2	SELECT REGEXP_INSTR ('The example shows how to use the REGEXP_INSTR function', 'ow', 16, 1, 0, 'i') FROM dual; --Результат: 20

В этом примере поиск шаблона 'ow' в строке начнется с позиции 16. В этом случае поиска шаблона он пропустит первые 15 символов строки.

Рассмотрим, как мы будем использовать функцию REGEXP_INSTR со столбцом таблицы и искать несколько символов.
Например:

SELECT REGEXP_INSTR (other_number, 'the', 1, 1, 0, 'i') FROM contacts;

1	SELECT REGEXP_INSTR (other_number, 'the', 1, 1, 0, 'i') FROM contacts;

В этом примере мы будем искать шаблон в поле other_number в таблице contacts.

Пример сопоставления несколько альтернатив.

Следующий пример, который мы рассмотрим, включает использование | шаблон. | шаблон используется как «ИЛИ», чтобы указать несколько альтернатив.
Например:

SELECT REGEXP_INSTR ('AeroSmith', 'a|e|i|o|u') FROM dual;

--Результат: 2

SELECT REGEXP_INSTR ('AeroSmith', 'a|e|i|o|u') FROM dual;

--Результат: 2

Этот пример вернет 2, потому что он ищет первую гласную (a, e, i, o или u) в строке. Поскольку мы не указали значение match_parameter, функция REGEXP_INSTR выполнит поиск с учетом регистра, что означает, что символ 'A' в 'AeroSmith' не будет сопоставлен.

Мы могли бы изменить наш запрос, чтобы выполнить поиск без учета регистра следующим образом:

SELECT REGEXP_INSTR ('AeroSmith', 'a|e|i|o|u', 1, 1, 0, 'i') FROM dual;

--Результат: 1

SELECT REGEXP_INSTR ('AeroSmith', 'a|e|i|o|u', 1, 1, 0, 'i') FROM dual;

--Результат: 1

Теперь, поскольку мы предоставили match_parameter = 'i', запрос вернет в качестве результата 1. На этот раз 'A' в 'AeroSmith' будет сопоставляться.

Рассмотрим, как вы будете использовать эту функцию со столбцом.

Итак, допустим, у нас есть таблица contact со следующими данными:

contact_id	last_name
1000	AeroSmith
2000	Joy
3000	Scorpions

Теперь давайте запустим следующий запрос:

SELECT contact_id, last_name, REGEXP_INSTR (last_name, 'a|e|i|o|u', 1, 1, 0, 'i') AS first_occurrence
FROM contacts;

1 2	SELECT contact_id, last_name, REGEXP_INSTR (last_name, 'a\|e\|i\|o\|u', 1, 1, 0, 'i') AS first_occurrence FROM contacts;

Результаты, которые будут возвращены запросом:

contact_id	last_name	first_occurrence
1000	AeroSmith	1
2000	Joy	2
3000	Scorpions	3

Пример совпадений на основе параметра nth_occurrence

Следующий пример, который мы рассмотрим, включает параметр nth_occurrence. Параметр nth_occurrence позволяет вам выбрать, в каком месте шаблона вы хотите вернуть позицию.

Первое вхождение

Давайте посмотрим, как найти первое вхождение шаблона в строку.
Например:

SELECT REGEXP_INSTR ('Regular expression', 'a|e|i|o|u', 1, 1, 0, 'i') FROM dual;

--Результат: 2

SELECT REGEXP_INSTR ('Regular expression', 'a|e|i|o|u', 1, 1, 0, 'i') FROM dual;

--Результат: 2

Этот пример вернет 2, потому что он ищет первое вхождение гласного (a, e, i, o или u) в строке.

Второе вхождение

Затем мы будем искать второе вхождение шаблона в строке.
Например:

SELECT REGEXP_INSTR ('Regular expression', 'a|e|i|o|u', 1, 2, 0, 'i') FROM dual;

--Результат: 4

SELECT REGEXP_INSTR ('Regular expression', 'a|e|i|o|u', 1, 2, 0, 'i') FROM dual;

--Результат: 4

Этот пример вернет 4, потому что он ищет второе вхождение гласного (a, e, i, o или u) в строке.

Третье вхождение

Например:

SELECT REGEXP_INSTR ('Regular expression', 'a|e|i|o|u', 1, 3, 0, 'i') FROM dual;

--Результат: 6

SELECT REGEXP_INSTR ('Regular expression', 'a|e|i|o|u', 1, 3, 0, 'i') FROM dual;

--Результат: 6

Этот пример вернет 6, потому что он ищет третье вхождение гласного (a, e, i, o или u) в строке.

Пример параметра return_option

Наконец, давайте посмотрим, как параметр return_option влияет на наши результаты.

Например:

SELECT REGEXP_INSTR ('Regular expression', 'exp', 1, 1, 0, 'i')
FROM dual;
--Результат: 9

SELECT REGEXP_INSTR ('Regular expression', 'exp', 1, 1, 0, 'i')

FROM dual;

--Результат: 9

В этом базовом примере мы ищем шаблон в строке, и поиск не зависит от регистра. Мы указали параметр return_option = 0, что означает, что будет возвращена позиция первого символа шаблона.

Теперь давайте изменим параметр return_option на 1 и посмотрим, что произойдет.
Например:

SELECT REGEXP_INSTR ('Regular expression', 'exp', 1, 1, 1, 'i') FROM dual;

--Результат: 12

SELECT REGEXP_INSTR ('Regular expression', 'exp', 1, 1, 1, 'i') FROM dual;

--Результат: 12

Параметр return_option = 1 сообщает функции REGEXP_INSTR, чтобы вернуть позицию символа, следующего за совпадающим шаблоном. В этом примере функция вернет 12.