Например, нужен полнотекстовый поиск адресов, представленных в пользовательской таблице, по данным справочника адресов Person.Address в поле AddressLine1 из базы данных AdventureWorks
Пользовательская таблица (SearchAddressResult):
id |
– id искомой записи |
SearchText |
– искомый текст (адрес) |
FoundAddressID |
– найденный AddressID в Person.Address |
В результате поиска необходимо отредактировать данные таблицы по полю FoundAddressID
Если в таблицу добавить поле Rank и в процессе поиска проставить ранг совпадения, то можно отфильтровать "плохо" найденные адреса.
Пример таблицы (SearchAddressResult):
Для поиска адресов вызываем функцию FREETEXTTABLE (table, field, search_string, language, top_n_by_rank)
table |
– Где искать: имя таблицы, в которой настроен полнотекстовый индекс; |
field |
– Где искать: имя индексированного поля (указывается в процессе настройки полнотекстового индекса); |
search_string |
– Что искать: строка поиска |
language |
– Язык. Указывается, если нужно искать с учетом особенностей языка (переносы, синонимы и пр.) |
top_n_by_rank |
– При указании аргумента top_n_by_rank функция вернет первые n в порядке сортировки по рангу совпадений (самый высокий – наилучшее совпадение; самый низкий – наихудшее) |
В приведенном примере аргументами FREETEXTTABLE будут следующие:
(Person.Address, AddressLine1, @SearchText, language 'English', 1)
Person.Address |
Таблица, в которой ищем (полнотекстовый индекс настроен); |
AddressLine1 |
Поле, по которому ищем (указан в качестве полнотекстового индекса) |
@SearchText |
Переменная процедуры поиска в цикле |
language 'English' |
Учет особенностей англ. языка |
1 |
По рангу совпадений нужна первая строка (строки с наилучшим совпадением всегда сверху) |
Пример запроса полнотекстового поиска по первому адресу из пользовательской таблицы:
use AdventureWorks
select * from freetexttable(Person.Address, AddressLine1, 'Landing Avenue, 3747', language 'English', 1)
Найденный адрес в Person.Address:
use AdventureWorks
Select * from Person.Address where AddressID =
(select [key] from freetexttable(Person.Address, AddressLine1, 'Landing Avenue, 3747', language 'English', 1))
Т.о., если бы задача решалась вручную, то вручную можно было бы проставить данные по первой строке
(поля SearchAddressResult.FoundAddressID и SearchAddressResult.Rank):
Процедура поиска и обновления данных по полям SearchAddressResult.FoundAddressID и SearchAddressResult.Rank в цикле:
USE AdventureWorks;
declare @FoundAddressID int, @SearchText nvarchar(1000);
DECLARE contact_cursor CURSOR FOR
SELECT id, SearchText FROM SearchAddressResult;
OPEN contact_cursor;
-- Perform the first fetch.
FETCH NEXT FROM contact_cursor
into @FoundAddressID, @SearchText;
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
--print @SearchText
exec
('update dbo.SearchAddressResult set [Rank] =
(select [Rank] from freetexttable(Person.Address, AddressLine1, '''+@SearchText+''', language ''English'', 1))
where id = '+@FoundAddressID );
exec
('update SearchAddressResult set FoundAddressId =
(select [key] from freetexttable(Person.Address, AddressLine1, '''+@SearchText+''', language ''English'', 1))
where id=' + @FoundAddressID);
FETCH NEXT FROM contact_cursor
into @FoundAddressID, @SearchText;
END
CLOSE contact_cursor;
DEALLOCATE contact_cursor;
Обновленные данные пользовательской таблицы:
Если построить запрос связующий SearchAddressResult.FoundAddressID и Person.Address.AddressID и отсортировать данные по рангу, то можно как-то понять с какого ранга результаты поиска действительно релевантны (в приведенном примере, очевидно, это те, ранг которых > 80):
use Adventureworks
select
id
,sar.SearchText
,pa.AddressLine1
,sar.[Rank]
from dbo.SearchAddressResult sar
left join Person.Address pa
on pa.AddressID = sar.FoundAddressID
order by [Rank]
В общем виде, в результате анализа релевантности найденных значений и рангов, можно добавить условие Rank > n в связующем запросе (или на уровне процедуры*):
use Adventureworks
select
sar.id
,sar.SearchText
,case when [Rank] > 80 then pa.AddressLine1 else null end as AddressLine1
from dbo.SearchAddressResult sar
left join Person.Address pa
ON pa.AddressID = sar.FoundAddressID
order by [Rank]
*
USE AdventureWorks;
declare @FoundAddressID int, @SearchText nvarchar(1000);
DECLARE contact_cursor CURSOR FOR
SELECT id, SearchText FROM SearchAddressResult;
OPEN contact_cursor;
-- Perform the first fetch.
FETCH NEXT FROM contact_cursor
into @FoundAddressID, @SearchText;
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
--print @SearchText
exec
('update SearchAddressResult set FoundAddressId =
(select case when [rank] > 80 then [key] else null end from freetexttable(Person.Address, AddressLine1, '''+@SearchText+''', language ''English'', 1))
where id=' + @FoundAddressID);
FETCH NEXT FROM contact_cursor
into @FoundAddressID, @SearchText;
END
CLOSE contact_cursor;
DEALLOCATE contact_cursor;