SQL, Анализ данных, Саморазвитие

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

Время прочтения: 4 мин.

В своей работе нам часто приходится анализировать текст, изложенный в свободной форме (назначения платежей, комментарии, адреса и др.) с целью вычленить определенную информацию. В 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

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

Советуем почитать