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

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

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

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

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

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

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

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