Бывает необходимо подсчитать, сколько раз определенная строка встречается в тексте. Однако формат этой строки может меняться. Например, существует несколько способов записи одного и того же номера телефона, и все они являются допустимыми форматами. Возможно ли это сделать в T-SQL? Давайте рассмотрим, как может помочь функция REGEXP_COUNT.
Решение
SQL Server 2025 в настоящее время выпущен в общедоступной предварительной версии (на момент написания доступна версия SQL Server 2025 CTP 2.1), и одним из заметных улучшений языка запросов T-SQL является поддержка регулярных выражений (regex). В статье «Функция REGEXP_LIKE в SQL Server 2025» представлена функция REGEXP_LIKE, которая похожа на встроенную функцию LIKE, но более мощная. В данной статье используется функция REGEXP_COUNT для демонстрации того, как мы можем подсчитать вхождения определенного шаблона в тексте.
Примечание: Эта статья написана с использованием SQL Server 2025 CTP 2.0. Между CTP 2.0 и 2.1 нет различий в поддержке регулярных выражений. Если вы также хотите попробовать SQL Server 2025, вы можете воспользоваться следующими руководствами для начала работы:
- Install SQL Server 2025 Demo Environment in Azure
- How to Install the AdventureWorks Sample Databases for SQL Server 2025
Если вы хотите опробовать поддержку регулярных выражений, но у вас нет SQL Server 2025, та же функциональность T-SQL также доступна в Azure SQL DB, Azure SQL Managed Instance и Fabric SQL DB.
Как и со всеми предварительными функциями, функциональность и/или поведение могут измениться при выпуске окончательного продукта.
Как подсчитать вхождения строки
Подсчет вхождений определенной строки в тексте — распространенная задача, и её легко решить, когда сама строка фиксирована. Например, предположим, что мы хотим узнать, сколько раз слово «database» встречается в следующем тексте:
“SQL (Structured Query Language) is a standardized programming language used to manage and manipulate relational databases. It allows users to perform operations such as querying data, creating tables, updating records, and managing database structures. SQL itself is not a database or a server; it is the language used to interact with databases.
SQL Server, on the other hand, is a Relational Database Management System (RDBMS) developed by Microsoft. It is a software application that uses SQL as its primary query language to store, retrieve, and manage data in relational databases. SQL Server provides tools and features for database management, such as data storage, transaction processing, and security.”
Найти количество вхождений
Мы можем найти количество вхождений определенного слова, вычислив разницу между полной длиной текста и тем же текстом, но с удаленным словом, а затем разделив эту разницу на длину искомого слова. Простой пример: есть фраза «MSSQLTips.com» и мы хотим узнать, сколько раз встречается «M» (без учета регистра).
[ «MSSQLTips.com» (13) – «SSQLTips.co» (11)] / [«M» (1)] = 2
В T-SQL это выглядит так:
DECLARE @mytext VARCHAR(4000) = '...текст для поиска...';
DECLARE @searchword VARCHAR(100) = 'database';
SELECT NumberOfOccurrences =
(LEN(@mytext) /* полная длина */
- LEN(REPLACE(@mytext,@searchword,'')) /* полная длина с удаленным искомым словом */
) / LEN(@searchword); /* разделить на длину искомого слова*/
Слово «database» встречается в тексте 7 раз.
Поиск других значений
Но что, если искомая фраза не фиксирована? Что, если вы ищете шаблон, такой как почтовые индексы, номера телефонов, адреса электронной почты или номера социального страхования? Существуют тысячи или миллионы возможных значений для каждого из них, и часто есть несколько способов форматирования одного и того же текста, и все они допустимы. Для иллюстрации, возьмём несколько примеров допустимых форматов номеров мобильных телефонов в Бельгии:
- +32472123456
- 0472 12 34 56
- 0472/123 456
- (+32) 472 12 34 56
Регулярные выражения могут помочь справиться с такими проблемами. Вместо указания точной строки вы указываете формат. Новая функция REGEXP_COUNT может подсчитать, сколько раз указанный шаблон регулярного выражения встречается в выражении. Её синтаксис следующий:
REGEXP_COUNT(строковое_выражение, регулярное_выражение [, начало [, флаги]])
Необязательный параметр "начало" дает вам начальную точку поиска, что, по сути, является способом пропустить x символов выражения. Необязательные параметры флагов объясняются в статье «Функция REGEXP_LIKE в SQL Server 2025».
Найти количество почтовых индексов США
Предположим, мы хотим найти количество почтовых индексов США, которые встречаются в отзывах о ресторанах. Для почтового индекса США возможны разные форматы, например, пятизначный почтовый индекс и ZIP+4. Я попросил ChatGPT создать 100 случайных отзывов о ресторанах, и некоторые из них должны были включать почтовые индексы, причем формат может варьироваться.
Вы можете скачать файл .sql со 100 операторами INSERT здесь.
Как писали на StackOverflow, для обнаружения почтовых индексов можно использовать это регулярное выражение: ^\d{5}(?:[-\s]\d{4})?$ Однако символы ^ и $ обозначают начало и конец строки, и с их включением мы вообще не получаем совпадений. Если мы удалим их, мы получим правильные совпадения. С помощью следующего скрипта T-SQL мы можем найти, сколько раз почтовый индекс упоминается в каждом отзыве:
SELECT ReviewID, NumberOfZipCodes = REGEXP_COUNT(ReviewText, '\d{5}([-]|\s*)?(\d{4})?')
FROM dbo.Reviews;
При просмотре фактических отзывов мы видим, что разные форматы были успешно идентифицированы:
Имейте в виду, что хотя REGEX_COUNT выглядит как агрегатная функция (подобно функции COUNT), она таковой не является. Функцию нельзя использовать вместе с предложением GROUP BY:
Комментариев нет:
Отправить комментарий