Как заставить ведущие полнотекстовые поисковые запросы работать в SQL Server?
Примечание: я использую возможности полнотекстового поиска SQL, предложения CONTAINS и все - знак * в полнотекстовом символе *, % только для предложений LIKE.
Сейчас я читал в нескольких местах, что поиск с "подстановочными символами" (например, использование "*overflow" для сопоставления со "stackru") не поддерживается в MS SQL. Я рассматриваю возможность использования функции CLR для добавления соответствия регулярному выражению, но мне любопытно посмотреть, какие другие решения могут быть у людей.
Дополнительная информация: Вы можете добавить звездочку только в конце слова или фразы. - вместе с моим эмпирическим опытом: при сопоставлении "myvalue", "my *" работает, но "(звездочка) значение" не возвращает совпадений, когда выполняет запрос так просто:
SELECT * FROM TABLENAME WHERE CONTAINS(TextColumn, '"*searchterm"');
Таким образом, моя потребность в обходном пути. Я использую поиск на своем сайте только на реальной поисковой странице - поэтому он должен работать в основном так же, как работает Google (в глазах пользователя типа Джо Сикспака). Не так сложно, но такой матч действительно не должен провалиться.
13 ответов
Обходной путь только для лидирующего группового символа:
- сохранить перевернутый текст в другом поле (или в материализованном виде)
- создать полнотекстовый индекс для этого столбца
найти перевернутый текст с помощью *
SELECT * FROM TABLENAME WHERE CONTAINS(TextColumnREV, '"mrethcraes*"');
Конечно, есть много недостатков, только для быстрого обхода...
Не говоря уже о CONTAINSTABLE...
Проблема с ведущими символами подстановки: они не могут быть проиндексированы, поэтому вы выполняете полное сканирование таблицы.
Можно использовать подстановочный знак "*" в конце слова или фразы (поиск по префиксу).
Например, этот запрос найдет все "базы данных", "базы данных", "базы данных"...
SELECT * FROM SomeTable WHERE CONTAINS(ColumnName, '"datab*"')
Но, к сожалению, поиск по лидирующим групповым символам невозможен.
Например, этот запрос не найдет "базу данных"
SELECT * FROM SomeTable WHERE CONTAINS(ColumnName, '"*abase"')
Чтобы, возможно, внести ясность в эту тему, из моего тестирования на 2008 R2, Франьо прав в приведенном выше. При работе с полнотекстовым поиском, по крайней мере, при использовании фразы CONTAINS, вы не можете использовать ведущий , только трейлинг функционально. * подстановочный знак, а не% в полном тексте.
Некоторые предположили, что * игнорируется. Похоже, что это не так, мои результаты показывают, что конечная функциональность * работает. Я думаю, что ведущие * игнорируются двигателем.
Однако моя дополнительная проблема заключается в том, что тот же запрос с завершающим символом *, использующий полный текст с подстановочными знаками, работал относительно быстро в 2005 году (20 секунд) и замедлился до 12 минут после переноса базы данных в 2008 R2. Похоже, что по крайней мере еще один пользователь имел схожие результаты, и он создал пост на форуме, который я добавил... FREETEXT все еще работает быстро, но что-то "кажется" изменилось в связи с тем, что процессы 2008 тянутся * в CONTAINS. Они дают всевозможные предупреждения в помощнике по обновлению о том, что они "улучшили" полный текст, чтобы ваш код мог сломаться, но, к сожалению, они не дают вам никаких особых предупреждений об определенном устаревшем коде и т. Д.... просто отказ от ответственности за то, что они изменили его, Используйте на свой риск.
http://social.msdn.microsoft.com/Forums/ar-SA/sqlsearch/thread/7e45b7e4-2061-4c89-af68-febd668f346c
Может быть, это ближайший ответ MS, связанный с этими проблемами... http://msdn.microsoft.com/en-us/library/ms143709.aspx
Следует иметь в виду, что передовые подстановочные запросы имеют значительный выигрыш в производительности по сравнению с другими подстановочными знаками.
Подстановочный знак в SQL Server является % подписать, и он работает просто отлично, ведущий, трейлинг или иначе.
Тем не менее, если вы собираетесь заниматься каким-либо серьезным полнотекстовым поиском, я бы подумал об использовании возможностей полнотекстового индекса. С помощью % а также _ подстановочные знаки приведут к серьезному снижению производительности вашей базы данных.
Из электронной документации по SQL Server:
Чтобы написать полнотекстовые запросы в Microsoft SQL Server 2005, вы должны научиться использовать предикаты CONTAINS и FREETEXT Transact-SQL, а также функции с набором строк CONTAINSTABLE и FREETEXTTABLE.
Это означает, что все запросы, написанные выше с% и _, не являются допустимыми полнотекстовыми запросами.
Вот пример того, как выглядит запрос при вызове функции CONTAINSTABLE.
ВЫБЕРИТЕ RANK, * FROM TableName, CONTAINSTABLE (TableName, *, ' "*WildCard" ') searchTable WHERE [KEY] = TableName.pk ЗАКАЗАТЬ НА searchTable.RANK DESC
Чтобы функция CONTAINSTABLE знала, что я использую поиск по шаблону, мне нужно заключить его в двойные кавычки. Я могу использовать подстановочный знак * в начале или конце. Есть много других вещей, которые вы можете сделать, когда создаете строку поиска для функции CONTAINSTABLE. Вы можете искать слово рядом с другим словом, искать слова со словами (диск = диски, водил, водил и водил) и искать синоним другого слова (у металла могут быть синонимы, такие как алюминий и сталь).
Я просто создал таблицу, поместил в нее полнотекстовый индекс и выполнил пару тестовых поисков, и у меня не возникло проблем, поэтому поиск по шаблону работает как задумано.
[Обновить]
Я вижу, что вы обновили свой вопрос и знаете, что вам нужно использовать одну из функций.
Вы по-прежнему можете искать с подстановочным знаком в начале, но если слово не является полным словом после подстановочного знака, вам необходимо добавить еще один подстановочный знак в конце.
Example: "*ildcar" will look for a single word as long as it ends with "ildcar".
Example: "*ildcar*" will look for a single word with "ildcar" in the middle, which means it will match "wildcard". [Just noticed that Markdown removed the wildcard characters from the beginning and ending of my quoted string here.]
[Обновление № 2]
Дейв Уорд - Использование символа подстановки с одной из функций не должно быть большим успехом. Если я создал строку поиска только с "*", он не вернет все строки, в моем тестовом случае он вернул 0 записей.
В качестве параметра хранимой процедуры вы можете использовать его как:
ALTER procedure [dbo].[uspLkp_DrugProductSelectAllByName]
(
@PROPRIETARY_NAME varchar(10)
)
as
set nocount on
declare @PROPRIETARY_NAME2 varchar(10) = '"' + @PROPRIETARY_NAME + '*"'
select ldp.*, lkp.DRUG_PKG_ID
from Lkp_DrugProduct ldp
left outer join Lkp_DrugPackage lkp on ldp.DRUG_PROD_ID = lkp.DRUG_PROD_ID
where contains(ldp.PROPRIETARY_NAME, @PROPRIETARY_NAME2)
Только к вашему сведению, Google не выполняет поиск или усечение подстрок, ни правых, ни левых. У них есть подстановочный знак * для поиска неизвестных слов во фразе, но не слова.
Google, наряду с большинством полнотекстовых поисковых систем, устанавливает инвертированный индекс на основе алфавитного порядка слов со ссылками на их исходные документы. Двоичный поиск быстро работает даже для огромных индексов. Но в этом случае действительно трудно выполнить усечение влево, потому что оно теряет преимущество индекса.
Если у вас есть доступ к списку слов механизма полнотекстового поиска, вы можете выполнить поиск в этом списке по принципу "лайк" и сопоставить базу данных с найденными словами, например, таблицу "слова" со следующими словами:
pie
applepie
spies
cherrypie
dog
cat
Чтобы сопоставить все слова, содержащие "pie" в этой базе данных в таблице fts "full_text" с полем "text":
to-match <- SELECT word FROM words WHERE word LIKE '%pie%'
matcher = ""
a = ""
foreach(m, to-match) {
matcher += a
matcher += m
a = " OR "
}
SELECT text FROM full_text WHERE text MATCH matcher
Возможно, следующая ссылка предоставит окончательный ответ на использование подстановочных знаков: Выполнение поиска подстановочных знаков FTS.
Обратите внимание на отрывок, который гласит: "Однако, если вы укажете" Chain "или" Ch ain ", вы не получите ожидаемый результат. Звездочка будет рассматриваться как обычный знак пунктуации, а не как символ подстановки".
Когда дело доходит до полнотекстового поиска, за мои деньги ничто не сравнится с Lucene. Доступен порт.Net, совместимый с индексами, созданными в версии Java.
Нужно немного поработать над тем, чтобы создать / поддерживать индексы, но скорость поиска просто фантастическая, и вы можете создавать всевозможные интересные запросы. Даже скорость индексации довольно хорошая - мы просто полностью перестраиваем наши индексы один раз в день и не беспокоимся об их обновлении.
Например, эта функциональность поиска основана на Lucene.Net.
% Соответствует любому количеству символов _ Соответствует одному символу
Я никогда не использовал полнотекстовое индексирование, но вы можете выполнять довольно сложные и быстрые поисковые запросы, просто используя встроенные строковые функции T-SQL.
Используя символ "%", я искал в нашей базе данных что-то вроде следующего:
SELECT name FROM TblNames WHERE name LIKE '%overflow'
Использование этой формы или запроса иногда может быть медленным, но мы используем его только для случайного ручного поиска.