Поиск информации в тексте средствами MS SQL Server
В своей работе нам часто приходится анализировать текст, изложенный в свободной форме (назначения платежей, комментарии, адреса и др.) с целью вычленить определенную информацию. В excel для этого можно использовать текстовые формулы и регулярные выражения VBA. Кроме того, регулярные выражения и текстовые функции можно использовать в python и других языках программирования.
Но как быть, если объем информации измеряется миллионами записей в базе данных, а времени на выгрузку и анализ небольших порций данных нет? Использование Transact-SQL (который хоть и не поддерживает регулярные выражения в полной мере, имеет более расширенный функционал оператора like, чем стандартный SQL) позволит переложить вычисления на плечи СУБД и сэкономить время на копировании данных.
Для примера выгрузили отзывы клиентов со страницы www.banki.ru/services/responses/bank/sberbank/ и импортировали данные в MSSQL 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
Описанные алгоритмы конечно же имеют погрешность (количество подряд идущих цифр не всегда однозначно определяет их суть, да и символ «@» встречается не только в адресах электронной почты), но в определенных ситуациях позволяют сэкономить время и дать представление о наличии и объеме искомой информации в текстовых полях.
Непонят зачем так сложно.
Если задача исследовать данные - возьмите Elastic Search, с ним можно быстро исследовать и искать данные.
На малом объёме данных (да и на большом в миллионы строк) можно тупо выгрузить поля в файлы и распаралелить поиск на любимом ЯП без сложностей с SQL и диалектами.
Если данных будет очень много то этот способ с like будет тормозить...
Интересный подход, особенно если применить его для снижения нагрузки на сервер (уменьшить запросы).
Зачет.
Спасибо!
Непонят зачем так сложно.
Если задача исследовать данные - возьмите Elastic Search, с ним можно быстро исследовать и искать данные.
На малом объёме данных (да и на большом в миллионы строк) можно тупо выгрузить поля в файлы и распаралелить поиск на любимом ЯП без сложностей с SQL и диалектами.
Если данных будет очень много то этот способ с like будет тормозить...
Artem, спасибо за ценный совет!