Автор: abhimantiwari, Regex support for LOB types in T-SQL—available in Azure SQL & SQL Server 2025
Краткий обзор. Собственные функции регулярных выражений (regex) в T-SQL теперь принимают входные данные типа varchar(max) и nvarchar(max) размером до 2 МБ во всех семи функциях регулярных выражений, включая две табличные функции (REGEXP_MATCHES и REGEXP_SPLIT_TO_TABLE). Эта возможность поставляется в SQL Server 2025 CU5. Вам больше не нужно разбивать файлы журналов, HTML-документы или большие JSON-нагрузки на 8000-байтовые фрагменты только для того, чтобы выполнить сопоставление с шаблоном.
1. Введение
Регулярные выражения уже давно являются краеугольным камнем современной обработки данных — они используются для проверки, синтаксического разбора, преобразования и извлечения структурированных сведений из неструктурированного текста. С выходом SQL Server 2025 регулярные выражения стали первоклассной возможностью T-SQL, устранив историческую необходимость полагаться на функции SQLCLR или обработку на уровне приложений.
Хотя в первоначальном выпуске собственные регулярные выражения стали широко доступны, большие объекты (LOB) ещё не поддерживались во всех функциях. CU5 закрывает этот пробел.
Под капотом T-SQL-регулярные выражения реализуют семантику POSIX ERE (расширенные регулярные выражения), дополненную набором Perl-стилевых возможностей, и работают на движке RE2. RE2 — это линейная, не использующая возвратов реализация, а это означает, что она не подвержена катастрофическим возвратам (класс атак типа «отказ в обслуживании», известный как ReDoS). Эта гарантия становится гораздо более важной, когда входные данные представляют собой 1,8-мегабайтный фрагмент журнала, а не 8000-байтовую строку.
Что нового в CU5
- Входные данные
varchar(max)иnvarchar(max)принимаются для каждой функции регулярных выражений. - Входная строка ограничена 2 МБ на вызов функции. Шаблон по-прежнему ограничен 8000 байтами, что намного больше, чем когда-либо понадобится для любого поддерживаемого регулярного выражения.
- Поведение согласовано между Azure SQL и SQL Server, поэтому код, написанный сегодня, полностью переносим.
Примечание. Ограничение в 2 МБ относится к входным данным, переданным при одном вызове функции, а не к столбцу или строке. Одно значение в столбце varchar(max) по-прежнему может хранить до 2 ГБ; ограничение заключается в том, что ни одно вычисление регулярного выражения не может потребить более 2 МБ этого значения.
Предварительные требования
- SQL Server 2025 CU5 или новее, или Azure SQL Database, или SQL Database в Fabric, или Azure SQL Managed Instance, настроенный с политикой обновления SQL Server 2025 / «Always-up-to-date».
- Для двух табличных функций (
REGEXP_MATCHESиREGEXP_SPLIT_TO_TABLE) требуется уровень совместимости базы данных 170, если только не включена конфигурация на уровне базы данныхALLOW_BUILTIN_TVF_IN_ALL_COMPAT_LEVELS(предварительная версия).
Проверка уровня совместимости (170 требуется для табличных функций):
SELECT name, compatibility_level FROM sys.databases WHERE name = DB_NAME();
-- При необходимости:
-- ALTER DATABASE [<your-database>] SET COMPATIBILITY_LEVEL = 170;
2. Работа с LOB-данными
Этот раздел демонстрирует возможности CU5 на реалистичных LOB-данных. Мы создадим таблицу LogEntries, столбец RawPayload которой содержит фрагменты вывода веб-сервера и приложения размером от нескольких килобайт до нескольких мегабайт, а также таблицу HtmlPages для примеров очистки HTML.
2.1 Создание образца схемы и данных
IF OBJECT_ID('dbo.LogEntries', 'U') IS NOT NULL DROP TABLE dbo.LogEntries; IF OBJECT_ID('dbo.HtmlPages', 'U') IS NOT NULL DROP TABLE dbo.HtmlPages; CREATE TABLE dbo.LogEntries ( LogId BIGINT IDENTITY(1,1) PRIMARY KEY, Source SYSNAME NOT NULL, IngestedAt DATETIME2(3) NOT NULL DEFAULT SYSUTCDATETIME(), RawPayload VARCHAR(MAX) NOT NULL -- LOB столбец ); CREATE TABLE dbo.HtmlPages ( PageId INT IDENTITY(1,1) PRIMARY KEY, Url NVARCHAR(2048) NOT NULL, Body NVARCHAR(MAX) NOT NULL -- LOB столбец (юникод) );Теперь сгенерируем реалистично большие строки.
-- Синтетическая нагрузка журнала доступа к веб-серверу (~252 КБ в строке 1, плюс отдельная строка ~586 КБ).
DECLARE @logLine VARCHAR(500) =
'127.0.0.1 - alice [21/May/2026:10:15:32 +0000] "GET /api/orders/42 HTTP/1.1" 200 1532 ' +
'user-agent="Mozilla/5.0" ip=10.0.0.7 email=alice@contoso.com card=4111-1111-1111-1234' + CHAR(10);
DECLARE @bigLog VARCHAR(MAX) =
REPLICATE(CAST(@logLine AS VARCHAR(MAX)), 1500) -- ~252 КБ
+ '127.0.0.1 - mallory [21/May/2026:10:16:01 +0000] "POST /login HTTP/1.1" 500 0 ' +
'ip=203.0.113.99 ssn=123-45-6789' + CHAR(10);
INSERT INTO dbo.LogEntries (Source, RawPayload) VALUES
('web-01', @bigLog), -- ~252 КБ
('web-02', REPLICATE(CAST('OK ' AS VARCHAR(MAX)), 200000)); -- ~586 КБ
-- Синтетическая HTML-страница (~775 КБ / ~396 000 символов).
DECLARE @htmlChunk NVARCHAR(MAX) =
N'<div class="row"><p>Hello <b>world</b>! Contact <a href="mailto:bob@contoso.com">bob</a>.</p></div>';
INSERT INTO dbo.HtmlPages (Url, Body) VALUES
(N'https://contoso.example/page-1',
N'<html><head><title>Big Page</title></head><body>'
+ REPLICATE(@htmlChunk, 4000)
+ N'</body></html>');
-- Подтверждение размеров в байтах.
SELECT LogId, Source, DATALENGTH(RawPayload) AS PayloadBytes FROM dbo.LogEntries;
SELECT PageId, DATALENGTH(Body) AS BodyBytes, LEN(Body) AS BodyChars FROM dbo.HtmlPages;
Результаты:
LogEntries
- LogId 1, Source web-01, PayloadBytes 258 110
- LogId 2, Source web-02, PayloadBytes 600 000
HtmlPages
- PageId 1, BodyBytes 792 124, BodyChars 396 062
До CU5 попытка передать любую из этих нагрузок в REGEXP_REPLACE, REGEXP_SUBSTR, REGEXP_MATCHES или REGEXP_SPLIT_TO_TABLE привела бы к ошибке несоответствия типов или потребовала бы усечения с помощью LEFT(RawPayload, 8000). Теперь те же запросы работают «из конца в конец».
2.2 REGEXP_LIKE — Фильтрация строк по LOB-содержимому
-- Найти записи журнала, содержащие хотя бы один ответ HTTP 5xx.
SELECT LogId, Source, DATALENGTH(RawPayload) AS PayloadBytes
FROM dbo.LogEntries
WHERE REGEXP_LIKE(RawPayload, '"[A-Z]+\s[^"]+\sHTTP/1\.[01]"\s5[0-9]{2}\s');
Результат: LogId 1, Source web-01, PayloadBytes 258 110.
Примечание. REGEXP_LIKE сама по себе требует уровня совместимости базы данных 170. Другие скалярные функции регулярных выражений (REGEXP_COUNT, REGEXP_INSTR, REGEXP_REPLACE, REGEXP_SUBSTR) доступны на всех уровнях совместимости.
2.3 REGEXP_COUNT — Подсчёт в масштабе
-- Подсчёт GET-запросов, POST-запросов и ответов 5xx в LOB-нагрузке.
SELECT LogId,
Source,
REGEXP_COUNT(RawPayload, '"GET\s') AS Gets,
REGEXP_COUNT(RawPayload, '"POST\s') AS Posts,
REGEXP_COUNT(RawPayload, '\s5[0-9]{2}\s') AS ServerErrors
FROM dbo.LogEntries;
Результаты:
- LogId 1: Gets = 1500, Posts = 1, ServerErrors = 1
- LogId 2: Gets = 0, Posts = 0, ServerErrors = 0
2.4 REGEXP_INSTR — Поиск позиции первой ошибки
SELECT LogId,
Source,
REGEXP_INSTR(RawPayload, '\s5[0-9]{2}\s', 1, 1, 0) AS FirstErrorPos
FROM dbo.LogEntries;
Результат: LogId 1: FirstErrorPos = 258 072; LogId 2: 0.
2.5 REGEXP_REPLACE — Редактирование конфиденциальных данных на месте
Маскировка PII в LOB-нагрузках была одним из наиболее востребованных сценариев CU5. До CU5 требовалась специальная процедура для замены фрагментами; теперь это одно выражение.
-- Маскировка токенов кредитных карт, номеров SSN и адресов электронной почты
SELECT LogId,
REGEXP_REPLACE(
REGEXP_REPLACE(
REGEXP_REPLACE(
RawPayload,
'\b[0-9]{4}[- ]?[0-9]{4}[- ]?[0-9]{4}[- ]?[0-9]{4}\b',
'---- ---- ---- ----'),
'\b[0-9]{3}-[0-9]{2}-[0-9]{4}\b',
'--- -- ----'),
'\b[A-Za-z0-9._%+\-]+@[A-Za-z0-9.\-]+\.[A-Za-z]{2,}\b',
'[redacted-email]'
) AS RedactedPayload
FROM dbo.LogEntries;
-- Или удаление всех HTML-тегов из nvarchar(max)-страницы за один вызов
SELECT PageId,
LEN(Body) AS OriginalLen,
LEN(REGEXP_REPLACE(Body, N'<[^>]+>', N'')) AS TextOnlyLen
FROM dbo.HtmlPages;
Результат для HtmlPages: PageId 1, OriginalLen 396 062, TextOnlyLen 100 008.
2.6 REGEXP_SUBSTR — Извлечение одного значения
-- Извлечение первого IPv4-адреса из каждой нагрузки журнала.
SELECT LogId,
REGEXP_SUBSTR(RawPayload,
'\b(?:[0-9]{1,3}\.){3}[0-9]{1,3}\b',
1,
1,
'c',
0
) AS FirstIp
FROM dbo.LogEntries;
Результат: LogId 1: FirstIp = 127.0.0.1; LogId 2: NULL.
2.7 REGEXP_MATCHES — Все совпадения, набор-ориентированно
Здесь комбинация табличной функции и LOB даёт наибольший прирост продуктивности: извлечение каждого структурированного значения из мегабайта неструктурированного текста в одном набор-ориентированном запросе, без круговых путей к клиенту.
SELECT l.LogId,
m.match_id,
m.match_value AS EmailFound
FROM dbo.LogEntries AS l
CROSS APPLY REGEXP_MATCHES(
l.RawPayload,
'\b[A-Za-z0-9._%+\-]+@[A-Za-z0-9.\-]+\.[A-Za-z]{2,}\b'
) AS m
ORDER BY l.LogId, m.match_id;
Захватывающие группы ещё более полезны — вы можете проецировать части каждой строки журнала в столбцы, читая JSON-документ substring_matches:
SELECT l.LogId,
m.match_id,
JSON_VALUE(m.substring_matches, '$[0].value') AS Ip,
JSON_VALUE(m.substring_matches, '$[1].value') AS UserName,
JSON_VALUE(m.substring_matches, '$[2].value') AS Status,
JSON_VALUE(m.substring_matches, '$[3].value') AS Bytes
FROM dbo.LogEntries AS l
CROSS APPLY REGEXP_MATCHES(
l.RawPayload,
'^([0-9.]+)\s-\s(\S+)\s\[[^\]]+\]\s"[^"]+"\s([0-9]{3})\s([0-9]+)',
'm'
) AS m
ORDER BY l.LogId, m.match_id;
2.8 REGEXP_SPLIT_TO_TABLE — Разбиение LOB на строки
SELECT l.LogId,
s.ordinal AS LineNo,
s.value AS LineText
FROM dbo.LogEntries AS l
CROSS APPLY REGEXP_SPLIT_TO_TABLE(l.RawPayload, '\r?\n') AS s
WHERE l.LogId = 1
AND s.value <> ''
ORDER BY s.ordinal;
2.9 Потолок в 2 МБ — стратегии для больших входных данных
Ограничение в 2 МБ применяется к входной строке одного вызова регулярного выражения. Если значение, переданное функции регулярного выражения, превышает 2 МБ, вызов вызывает ошибку (номер ошибки 19311, уровень серьёзности 16). На практике 2 МБ — это щедрый потолок: один файл журнала или HTML-документ такого размера уже необычен, и большинство реальных LOB-данных с комфортом находятся ниже этого значения. Когда отдельные значения всё же превышают лимит, наиболее надёжный подход — разделить их на более мелкие логические единицы до того, как они попадут в столбец, который вы хотите запросить.
Важно. Ограничение в 2 МБ измеряется в байтах, а не в символах, и количество байтов основано на кодировке UTF-8 входных данных, независимо от объявленного типа столбца. Чтобы измерить длину в байтах UTF-8, которую фактически проверяет ограничение, приведите значение к varchar(max) с использованием сортировки UTF-8 и получите его DATALENGTH:
SELECT DATALENGTH(
CONVERT(varchar(max),
Body COLLATE Latin1_General_100_CI_AS_SC_UTF8)
) AS Utf8Bytes
FROM dbo.HtmlPages;
2.10 Очистка
DROP TABLE IF EXISTS dbo.LogEntries;
DROP TABLE IF EXISTS dbo.HtmlPages;
3. Резюме
Краткая справка по функциям
REGEXP_LIKE— логическое значение (предикат). Поддержка LOB в CU5: Да. Обычное применение: фильтрация строк.REGEXP_COUNT—int. Поддержка LOB: Да. Подсчёт вхождений.REGEXP_INSTR—int. Поддержка LOB: Да. Позиция n-го совпадения.REGEXP_REPLACE— строка. Поддержка LOB: Да. Маскировка, очистка, нормализация.REGEXP_SUBSTR— строка. Поддержка LOB: Да. Извлечение одного значения.REGEXP_MATCHES(табличная) — набор. Поддержка LOB: Да. Извлечение всех совпадений.REGEXP_SPLIT_TO_TABLE(табличная) — набор. Поддержка LOB: Да. Разбиение LOB на строки.
Для дальнейшего чтения см. официальную документацию Microsoft по каждой из указанных функций.
Заключительная мысль. Собственные регулярные выражения уже были значительным улучшением качества жизни, когда стали общедоступными. CU5 завершает картину: каждая функция, любой размер входных данных до 2 МБ, любая форма — скалярная или табличная. В следующий раз, когда вас посетит желание экспортировать столбец из базы данных, чтобы выполнить к нему grep, сначала попробуйте одну из семи функций регулярных выражений.
Комментариев нет:
Отправить комментарий