Цель этой статьи — показать, как можно находить несколько совпадений с использованием регулярных выражений SQL Server, чтобы примеры были более наглядными (особенно в последующих частях).
Существует несколько функций, которые позволяют в выводе результата показать несколько совпадений фрагмента строки или шаблона:
- REGEXP_REPLACE – возвращает измененную исходную строку, замененную строкой замены, в которой найдено вхождение шаблона регулярного выражения. Если совпадения не найдены, функция возвращает исходную строку.
- REGEXP_SUBSTR – возвращает одно вхождение подстроки в анализируемой строке, которое соответствует шаблону регулярного выражения. Если совпадение не найдено, возвращается NULL.
- REGEXP_INSTR – возвращает начальную или конечную позицию соответствующей подстроки в зависимости от значения аргумента return_option.
- REGEXP_COUNT – подсчитывает количество совпадений шаблона регулярного выражения в строке.
- REGEXP_SPLIT_TO_TABLE – используется аналогично функции SPLIT_TO_TABLE, но возвращает таблицу строк, разделенную шаблоном регулярных выражений. Если шаблон не соответствует, функция возвращает строку.
- REGEXP_MATCHES – возвращает таблицу захваченных подстрок, которые соответствуют шаблону регулярного выражения строке. Если совпадение не найдено, функция не возвращает строку.
По сути, это все функции регулярных выражений, кроме той, о которой я уже писал ранее — REGEXP_LIKE. В этой статье я хочу рассмотреть функцию REGEXP_MATCHES, чтобы продемонстрировать её возможности (я ошибочно полагал, что вместо неё стоит использовать REGEXP_SPLIT_TO_TABLE, так как её название похоже на то, что было нужно, и не смотря на то, что само название функции SPLIT_TO_TABLE должно было подсказать обратное).
В этой статье мы перестанем изучать новые способы написания шаблонов поиска с использованием регулярных выражений и добавим новый инструмент: использование REGEXP_MATCHES, чтобы увидеть, сколько раз строка совпадает с шаблоном.
Несколько совпадений
Например, рассмотрим строку 1234567890. Если в запросе использовать REGEXP_LIKE :
SELECT CASE WHEN REGEXP_LIKE('1234567890','\d') THEN 1 ELSE 0 END;
Результатом будет 1, так как эта строка содержит хотя бы одну цифру. Но на самом деле совпадению будет больше, что подтверждается другими функциями. С помощью REGEXP_MATCHES мы можем увидеть все совпадения:
SELECT *
FROM REGEXP_MATCHES('123456789','\d');
Результат содержит несколько столбцов, которые предоставляют информацию о совпадениях:
match_id start_position end_position match_value substring_matches
--------- -------------- ------------ ----------- -----------------------
1 1 1 1 [{"value":"1","start":1,"length":1}]
2 2 2 2 [{"value":"2","start":2,"length":1}]
3 3 3 3 [{"value":"3","start":3,"length":1}]
4 4 4 4 [{"value":"4","start":4,"length":1}]
5 5 5 5 [{"value":"5","start":5,"length":1}]
6 6 6 6 [{"value":"6","start":6,"length":1}]
7 7 7 7 [{"value":"7","start":7,"length":1}]
8 8 8 8 [{"value":"8","start":8,"length":1}]
9 9 9 9 [{"value":"9","start":9,"length":1}]
Столбцы означают:
- match_id – уникальный идентификатор совпадения.
- start_position – позиция первого символа совпадения.
- end_position – позиция последнего символа совпадения.
- match_value – значение, соответствующее шаблону.
- substring_matches – значение, начальная позиция и длина в формате JSON (по какой-то причине, которую я надеюсь понять в будущем).
Далее я буду включать только три столбца из вывода REGEXP_MATCHES: start_position, end_position, match_value, — так как этого достаточно для понимания, и это проще форматировать в статье.
Теперь рассмотрим более интересное выражение, которое ищет любую комбинацию из 2–3 цифр:
SELECT start_position, end_position, match_value
FROM REGEXP_MATCHES('123456789','\d{2,3}');
Заметьте, что функция не останавливается на совпадении из двух символов, а продолжает до трёх, прежде чем перейти к следующему результату:
start_position end_position match_value -------------- ------------ ----------- 1 3 123 4 6 456 7 9 789
А если использовать \d+, то будет совпадение с числом от одной до бесконечности цифр:
SELECT start_position, end_position, match_value
FROM REGEXP_MATCHES('123456789','\d+');
-- что то же самое, что '\d{1,N}' для любого N >= 9 в данном случае:
Вывод — одна строка со всеми символами, потому что каждый следующий символ продолжает удовлетворять шаблону:
start_position end_position match_value -------------- ------------ ----------- 1 9 123456789
Несколько совпадений в таблице
Теперь вместо анализа одной строки результата мы посмотрим на несколько строк в таблице. Вернёмся к таблице для примеров, её скрипт в Приложении ниже, и загрузим несколько строк:
TRUNCATE TABLE RegExTest;
INSERT INTO RegExTest (Value)
VALUES ('aa'),('aaa'),('aaaa'),('aaaaa'),
('baaaaaa'),('aaaaab'),('baaaaab'),
('bababab'),('bababac'),('cbababac'),
('b1ab2ab3ab4a');
Используем такой запрос:
SELECT Value
FROM RegExTest
WHERE REGEXP_LIKE(Value,'a{3,4}');
Он находит следующие строки:
Value ----------- aaa aaaa aaaaa aaaaab baaaaaa baaaaab
Если мы хотим посмотреть, как шаблон сопоставляется с набором строк, можно использовать CROSS APPLY, с помощью которого будем передавать значения в функцию REGEXP_MATCHES:
SELECT CAST(Value as nvarchar(15)) as Value,
REGEXOUT.start_position, REGEXOUT.end_position,
REGEXOUT.match_value
FROM RegExTest
CROSS APPLY REGEXP_MATCHES(Value,'a{3,4}') as REGEXOUT;
Результаты будут такими же, как и раньше, но теперь мы видим, что совпало, и его длину:
Value start_position end_position match_value -------------- -------------- ------------ --------------- aaa 1 3 aaa aaaa 1 4 aaaa aaaaa 1 4 aaaa aaaaab 1 4 aaaa baaaaaa 2 5 aaaa baaaaab 2 5 aaaa
Изменим выражение на a{2}:
SELECT cast(Value as nvarchar(15)) as Value,
REGEXOUT.start_position, REGEXOUT.end_position,
REGEXOUT.match_value
FROM RegExTest
CROSS APPLY REGEXP_MATCHES(Value,'a{2}') as REGEXOUT;
Теперь результаты для таблицы RegExTest те же, но совпадения теперь выглядят иначе (и строки из таблицы повторяются):
Value start_position end_position match_value -------------- -------------- ------------ ----------- aa 1 2 aa aaa 1 2 aa aaaa 1 2 aa aaaa 3 4 aa aaaaa 1 2 aa aaaaa 3 4 aa aaaaab 1 2 aa aaaaab 3 4 aa baaaaaa 2 3 aa baaaaaa 4 5 aa baaaaaa 6 7 aa baaaaab 2 3 aa baaaaab 4 5 aa
Заключение
В этой статье мы добавили новый инструмент, который позволяет видеть несколько совпадений, выводя их в виде таблицы. Этот инструмент я буду использовать чаще в будущих публикациях (особенно когда перейду от шаблонов к функциям, с которых начинал эту серию статей). Теперь у нас есть возможность показать все возможные совпадения регулярного выражения с литералом, переменной или даже значениями в таблице.
Приложение
USE TempDB;
GO
SET NOCOUNT ON;
-- Удаляем таблицу, если она существует
IF OBJECT_ID('dbo.RegExTest', 'U') IS NOT NULL
DROP TABLE dbo.RegExTest;
-- Создаём таблицу
CREATE TABLE dbo.RegExTest (
RegExTestId INT IDENTITY(1,1) CONSTRAINT PK_RegExTest PRIMARY KEY,
Value NVARCHAR(100) CONSTRAINT AK_RegExTest UNIQUE
);
Комментариев нет:
Отправить комментарий