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

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

Описание

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

Синтаксис

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

REGEXP_SUBSTR( string, pattern [, start_position [, nth_appearance [, 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, который является первым вхождением шаблона в строке. Если вы укажете 0 для этого параметра, все вхождения шаблона в строке будут заменены.
match_parameter
Необязательный. Это позволяет изменять поведение соответствия для условия REGEXP_REPLACE. Это может быть комбинацией следующих значений:

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

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

Функция REGEXP_SUBSTR возвращает строковое значение.
Если функция REGEXP_SUBSTR не обнаруживает какого-либо вхождения шаблона, она возвращает NULL.

Примечание

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

Применение

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

Oracle 12c, Oracle 11g, Oracle 10g

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

Начнем с извлечения первого слова из строки.
Например:

SELECT REGEXP_SUBSTR ('Google is a great search engine.', '(\S*)(\s)')
FROM dual;
--Результат: 'Google '

SELECT REGEXP_SUBSTR ('Google is a great search engine.', '(\S*)(\s)')

FROM dual;

--Результат: 'Google '

Этот пример вернет 'Google ', потому что он будет извлекать все символы без пробелов, как указано (\S*), а затем первый символ пробела, заданный (\s). Результат будет включать как первое слово, так и пробел после слова.

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

SELECT REGEXP_SUBSTR ('Google is a great search engine.', '(\S*)')
FROM dual;
--Результат: 'Google'

SELECT REGEXP_SUBSTR ('Google is a great search engine.', '(\S*)')

FROM dual;

--Результат: 'Google'

Этот пример вернет 'Google' без пробела в конце.

Если нам необходимо найти второе слово в строке, то изменим нашу функцию следующим образом:

SELECT REGEXP_SUBSTR ('Google is a great search engine.', '(\S*)(\s)', 1, 2)
FROM dual;
--Результат: 'is '

SELECT REGEXP_SUBSTR ('Google is a great search engine.', '(\S*)(\s)', 1, 2)

FROM dual;

--Результат: 'is '

Этот пример вернет 'is ' с пробелом в конце строки.
Если нам необходимо найти четвертое слово в строке, мы изменим нашу функцию следующим образом:

SELECT REGEXP_SUBSTR ('Google is a great search engine.', '(\S*)(\s)', 1, 4)
FROM dual;
--Результат: 'great '

SELECT REGEXP_SUBSTR ('Google is a great search engine.', '(\S*)(\s)', 1, 4)

FROM dual;

--Результат: 'great '

Этот пример вернет 'great ' с пробелом в конце строки.

Пример совпадения цифр

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

SELECT REGEXP_SUBSTR ('2, 4, и 10 числа для примера', '\d')
FROM dual;
--Результат: '2 '

SELECT REGEXP_SUBSTR ('2, 4, и 10 числа для примера', '\d')

FROM dual;

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

В этом примере будет извлечена первая цифра из строки, как указано в \d. В этом случае он будет соответствовать числу 2.

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

SELECT REGEXP_SUBSTR ('2, 4, и 10 числа для примера', '(\d)(\d)')
FROM dual;
--Результат: '10'

SELECT REGEXP_SUBSTR ('2, 4, и 10 числа для примера', '(\d)(\d)')

FROM dual;

--Результат: '10'

В этом примере будет выведено число, которое имеет две цифры, как указано в (\d)(\d). В этом случае он пропустит числовые значения 2 и 4 и вернет 10.

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

SELECT REGEXP_SUBSTR (address, '(\d)(\d)')
FROM contacts;

1 2	SELECT REGEXP_SUBSTR (address, '(\d)(\d)') FROM contacts;

В этом примере мы собираемся извлечь первое двузначное значение из поля address в таблице contacts.

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

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

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

SELECT REGEXP_SUBSTR ('AeroSmith', 'a|e|i|o|u')

FROM dual;

--Результат: 'e'

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

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

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

--Результат: 'A'

SELECT REGEXP_SUBSTR ('AeroSmith', 'a|e|i|o|u', 1, 1, 'i')

FROM dual;

--Результат: 'A'

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

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

contact_id	last_name
1000	AeroSmith
2000	Joy
3000	Scorpions

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

SELECT contact_id, last_name, REGEXP_SUBSTR (last_name, 'a|e|i|o|u', 1, 1, 'i') AS "First Vowel"
FROM contacts;

1 2	SELECT contact_id, last_name, REGEXP_SUBSTR (last_name, 'a\|e\|i\|o\|u', 1, 1, 'i') AS "First Vowel" FROM contacts;

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

contact_id	last_name	First Vowel
1000	AeroSmith	A
2000	Joy	o
3000	Scorpions	o

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

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

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

Рассмотрим, как извлечь первое вхождение шаблона в строку.
Например:

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

SELECT REGEXP_SUBSTR ('AeroSmith', 'a|e|i|o|u', 1, 1, 'i')

FROM dual;

--Результат: 'A'

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

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

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

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

SELECT REGEXP_SUBSTR ('AeroSmith', 'a|e|i|o|u', 1, 2, 'i')

FROM dual;

--Результат: 'e'

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

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

Например:

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

SELECT REGEXP_SUBSTR ('AeroSmith', 'a|e|i|o|u', 1, 3, 'i')

FROM dual;

--Результат: 'o'

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