Разработка
NTA

Поиск информации в тексте средствами MS SQL Server

В своей работе нам часто приходится анализировать текст, изложенный в свободной форме (назначения платежей, комментарии, адреса и др.) с целью вычленить определенную информацию. В excel для этого можно использовать текстовые формулы и регулярные выражения VBA. Кроме того, регулярные выражения и текстовые функции можно использовать в python и других языках программирования.

Но как быть, если объем информации измеряется миллионами записей в базе данных, а времени на выгрузку и анализ небольших порций данных нет? Использование Transact-SQL (который хоть и не поддерживает регулярные выражения в полной мере, имеет более расширенный функционал оператора like, чем стандартный SQL) позволит переложить вычисления на плечи СУБД и сэкономить время на копировании данных.

Для примера выгрузили отзывы клиентов со страницы www.banki.ru/services/responses/bank/sberbank/ и импортировали данные в MS SQL Server. Таблица banki_ru_messages содержит 3 поля: дату отзыва, заголовок и текст.

Простой поиск по ключевым словам, думаю, знаком многим:

select * from [dbo].[banki_ru_messages] where [Сообщение] like '%ипотека%'

Для поиска телефонных номеров можно искать последовательность из 11 подряд идущих цифр, предварительно удалив пробелы и символы «-”, «(“, “)», “/»:

alter table [dbo].[banki_ru_messages] add [Номер телефона] nvarchar(max) go update [dbo].[banki_ru_messages] set [Номер телефона]=replace(replace(replace(replace(replace([Сообщение], ' ', ''), '-', ''), '(', ''), ')', ''), '/', '') where replace(replace(replace(replace(replace([Сообщение], ' ', ''), '-', ''), '(', ''), ')', ''), '/', '') like '%[^0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][^0-9]%' or replace(replace(replace(replace(replace([Сообщение], ' ', ''), '-', ''), '(', ''), ')', ''), '/', '') like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][^0-9]%' or replace(replace(replace(replace(replace([Сообщение], ' ', ''), '-', ''), '(', ''), ')', ''), '/', '') like '%[^0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' go declare @t nvarchar(max) while exists (select top 1 [Номер телефона] from [dbo].[banki_ru_messages] where [Номер телефона] is not null and [Номер телефона] not like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][^0-9]%' and [Номер телефона] not like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' ) begin set @t =(select top 1 [Номер телефона] from [dbo].[banki_ru_messages] where [Номер телефона] is not null and [Номер телефона] not like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][^0-9]%' and [Номер телефона] not like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' ) if @t like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%' begin update [dbo].[banki_ru_messages] set [Номер телефона]=right([Номер телефона], len([Номер телефона])-12) where [Номер телефона] =@t end else begin update [dbo].[banki_ru_messages] set [Номер телефона]=right([Номер телефона], len([Номер телефона])-1) where [Номер телефона] =@t end end go update [dbo].[banki_ru_messages] set [Номер телефона]=left([Номер телефона], 11) where [Номер телефона] is not null go alter table [dbo].[banki_ru_messages] alter column [Номер телефона] nvarchar(11) go select * from [dbo].[banki_ru_messages] where [Номер телефона] is not null

Аналогичным способом, изменив количество цифр, можно искать номера счетов, ИНН, почтовые индексы.

Для поиска адресов электронной почты можно воспользоваться следующим кодом:

alter table [dbo].[banki_ru_messages] add [E-mail] nvarchar(max) go update [dbo].[banki_ru_messages] set [E-mail]=[Сообщение] where [Сообщение] like '%[A-z0-9]@[A-z0-9]%' go declare @t nvarchar(max) while exists (select top 1 [E-mail] from [dbo].[banki_ru_messages] where [E-mail] is not null and charindex(' ', [E-mail])>0) begin set @t=(select top 1 [E-mail] from [dbo].[banki_ru_messages] where [E-mail] is not null and charindex(' ', [E-mail])>0) if left(@t, charindex(' ', @t)-1) like '%[A-z0-9]@[A-z0-9]%' begin update [dbo].[banki_ru_messages] set [E-mail]=left(@t, charindex(' ', @t)-1) where [E-mail]=@t end else begin update [dbo].[banki_ru_messages] set [E-mail]=right(@t, len(@t)-charindex(' ', @t)+1) where [E-mail]=@t end end go select * from [dbo].[banki_ru_messages] where [E-mail] is not null or [ИНН] is not null or [Номер телефона] is not null

Описанные алгоритмы конечно же имеют погрешность (количество подряд идущих цифр не всегда однозначно определяет их суть, да и символ «@» встречается не только в адресах электронной почты), но в определенных ситуациях позволяют сэкономить время и дать представление о наличии и объеме искомой информации в текстовых полях.

{ "author_name": "NTA", "author_type": "editor", "tags": [], "comments": 4, "likes": 0, "favorites": 6, "is_advertisement": false, "subsite_label": "dev", "id": 210060, "is_wide": true, "is_ugc": false, "date": "Wed, 17 Feb 2021 16:08:50 +0300", "is_special": false }
0
4 комментария
Популярные
По порядку
1

Интересный подход, особенно если применить его для снижения нагрузки на сервер (уменьшить запросы).
Зачет.

Ответить
0

Спасибо!

Ответить
1

Непонят зачем так сложно.
Если задача исследовать данные - возьмите Elastic Search, с ним можно быстро исследовать и искать данные.
На малом объёме данных (да и на большом в миллионы строк) можно тупо выгрузить поля в файлы и распаралелить поиск на любимом ЯП без сложностей с SQL и диалектами.
Если данных будет очень много то этот способ с like будет тормозить...

Ответить
0

Artem, спасибо за ценный совет!

Ответить

Комментарии

null