Функция REGEXP_SUBSTR извлекает части строки на основе шаблона регулярного выражения. Она имеет сходство с функцией SUBSTRING, но есть и важные (и интересные) различия. Эта функция возвращает N-ое вхождение подстроки, которая соответствует регулярному выражению.
Когда я начал писать десятую статью в серии о регулярных выражениях в SQL Server, я должен признаться: я не знал заранее, что именно эта функция делает. Классическая функция SUBSTRING принимает строго позиционные параметры. Задана строка, указываешь начальную позицию и количество символов — и получаешь результат. Никакого сопоставления с шаблоном. К счастью для вас, изучение этого материала у меня заняло совсем немного времени.
В одной из предыдущих статей, где речь шла о REGEXP_MATCHES, я показывал, как можно увидеть все совпадения, которые регулярное выражение находит в строке. REGEXP_SUBSTR в своей простой форме возвращает скалярный результат — то есть одно совпадение.
Синтаксис функции
REGEXP_SUBSTR
(
string_expression,
pattern_expression [, start [, occurrence [, flags [, group ] ] ] ]
)
В простейшем виде вы передаёте значение и шаблон. В следующем примере функция ищет первые три подряд идущих цифры:
SELECT REGEXP_SUBSTR('abc1234567890987654321','\d{3}');
Результат:
123
Следующий параметр задаёт начальную позицию поиска. Например, если начать поиск с четвёртого символа:
SELECT REGEXP_SUBSTR('abc1234567890987654321','\d{3}',4);
Результат остаётся тем же:
123
Поскольку индексация начинается с 1, символ «a» находится в первой позиции, а «c» — в третьей. Но если использовать 5:
SELECT REGEXP_SUBSTR('abc1234567890987654321','\d{3}',5);
Результат:
234
Хотя параметр стартовой позиции сам по себе не очень интересен (хотя наверняка есть сценарии, где он незаменим), следующий параметр — occurrence — действительно важен. С его помощью можно сделать то, что делает REGEXP_MATCHES, только возвращая по одному совпадению за вызов:
SELECT REGEXP_SUBSTR('abc1234567890987654321','\d{3}',1,1);
Это возвращает первые три цифры:
123
А третье вхождение:
SELECT REGEXP_SUBSTR('abc1234567890987654321','\d{3}',1,3);
Возвращает 7-й, 8-й и 9-й символы:
789
Можно заметить, что при использовании REGEXP_MATCHES с тем же значением результат будет аналогичен — вы увидите все совпадения, которые можно извлечь с помощью REGEXP_SUBSTR:
SELECT match_id, match_value,
REGEXP_SUBSTR('abc1234567890987654321','\d{3}',1,match_id)
AS REGEXP_SUBSTR_result
FROM REGEXP_MATCHES('abc1234567890987654321','\d{3}');
Результат:
match_id match_value REGEXP_SUBSTR_result
--------- -------------- ----------------------
1 123 123
2 456 456
3 789 789
4 098 098
5 765 765
6 432 432
Группы
Пропустим параметр flags (он работает так же, как описывалось для других функций регулярок ранее), следующий параметр — это группы.
Как я показывал на примере REGEXP_REPLACE, существует концепция групп, которая позволяет извлекать подмножество совпадения. В функции REGEXP_REPLACE это используется для форматирования результата с помощью заполнителей. В REGEXP_SUBSTR, как помните, можно напрямую извлекать части совпадения.
В следующем примере используется шаблон '(\d{1})(\d{2})'. Без указания группы он возвращает те же три символа, что и прежде. Но если задать группу 1 и 2, то первый столбец вернёт один символ, а второй — оставшиеся два.
SELECT match_id, match_value,
REGEXP_SUBSTR('abc1234567890987654321','(\d{1})(\d{2})',1,match_id)
AS REGEXP_SUBSTR,
REGEXP_SUBSTR('abc1234567890987654321','(\d{1})(\d{2})',1,match_id,'',1)
AS REGEXP_SUBSTR_1,
REGEXP_SUBSTR('abc1234567890987654321','(\d{1})(\d{2})',1,match_id,'',2)
AS REGEXP_SUBSTR_2
FROM REGEXP_MATCHES('abc1234567890987654321','(\d{1})(\d{2})');
Результат показывает, что группы разделяются:
match_id match_value REGEXP_SUBSTR REGEXP_SUBSTR_1 REGEXP_SUBSTR_2
---------- ------------ -------------- ---------------- ----------------
1 123 123 1 23
2 456 456 4 56
3 789 789 7 89
4 098 098 0 98
5 765 765 7 65
6 432 432 4 32
Простой пример
Рассмотрим таблицу с телефонными номерами (или тем, что должно быть телефонными номерами). Нужно извлечь код региона — первые три цифры, если данные соответствуют заданному формату:
CREATE TABLE #numbers(phoneNumber varchar(13));
INSERT INTO #numbers
VALUES ('111-111-1111'),('222-221-2222'),('333-333'),('44444444444');
SELECT phoneNumber,
REGEXP_SUBSTR(phoneNumber,'^(\d{3})-(\d{3})-(\d{4})$',1,1,'',1)
AS areaCode
FROM #numbers;
Результат:
phoneNumber areaCode
------------- -------------
111-111-1111 111
222-221-2222 222
333-333 NULL
44444444444 NULL
Обратите внимание, что здесь нельзя использовать выражение OR так, как можно было бы ожидать. Если добавить альтернативу для формата «4444444444», то всё равно будет возвращаться NULL:
SELECT phoneNumber,
REGEXP_SUBSTR(phoneNumber,
'^(\d{3})-(\d{3})-(\d{4})$|^(\d{3})(\d{3})(\d{4})$',1,1,'',1)
AS areaCode
FROM #numbers;
Результат тот же. Но если изменить группу на 4:
SELECT phoneNumber,
REGEXP_SUBSTR(phoneNumber,
'^(\d{3})-(\d{3})-(\d{4})$|^(\d{3})(\d{3})(\d{4})$',1,1,'',4)
AS areaCode
FROM @numbers;
Теперь только строка «4444444444» вернёт код региона:
phoneNumber areaCode
------------- -------------
111-111-1111 NULL
222-221-2222 NULL
333-333 NULL
4444444444 444
Комментариев нет:
Отправить комментарий