CONTAINS: примеры (SQL)

Базовые примеры использования

Поиск точного слова.

SELECT * FROM Articles
WHERE CONTAINS(article_text, 'база данных');

-- Возвращает строки, где в столбце article_text
-- встречается фраза "база данных".

Поиск с использованием префикса.

SELECT title FROM Documents
WHERE CONTAINS(content, '"производи*"');

-- Найдет "производитель", "производительность".

Использование логического оператора AND.

SELECT * FROM Products
WHERE CONTAINS(description, 'электронный AND книга');

-- Строки, где в описании есть оба слова.

Поиск с учетом словоформ (инфлективный поиск).

SELECT * FROM News
WHERE CONTAINS(text, 'FORMSOF(INFLECTIONAL, писать)');

-- Найдет "пишу", "писал", "написано".

Поиск по близости с NEAR.

SELECT id, snippet FROM Books
WHERE CONTAINS(book_text, '"структура" NEAR "данные"');

-- Строки, где слова "структура" и "данные"
-- находятся близко друг к другу в тексте.

Другие функции полнотекстового поиска в MS SQL

FREETEXT - выполняет более свободный поиск. Функция разбивает строку поиска на отдельные слова, генерирует их производные формы и ищет совпадения в любом порядке. Подходит для поиска по смыслу, а не по точной фразе.

SELECT * FROM Articles WHERE FREETEXT(text, 'управление базами данных');

CONTAINSTABLE и FREETEXTTABLE - возвращают таблицу с ключами и рангами релевантности (RANK). Их используют, когда нужен рейтинг совпадений для сортировки результатов по релевантности.

SELECT a.*, ct.[RANK]
FROM Articles a
INNER JOIN CONTAINSTABLE(Articles, text, 'SQL') AS ct
ON a.id = ct.[KEY]
ORDER BY ct.[RANK] DESC;

Выбор функции зависит от задачи. CONTAINS применяют для точного и сложного поиска с операторами. FREETEXT используют для простого семантического поиска. CONTAINSTABLE/FREETEXTTABLE нужны для ранжирования.

Аналоги в других СУБД и языках

MySQL: Оператор MATCH ... AGAINST в режиме естественного языка или булевого поиска. Требует полнотекстовый индекс типа FULLTEXT.

SELECT * FROM articles
WHERE MATCH (title, body) AGAINST ('+SQL -база' IN BOOLEAN MODE);

-- Булев поиск: должно быть "SQL", не должно быть "база".

PostgreSQL: Использует оператор @@ с типами tsvector и tsquery. Очень гибкая система с поддержкой словарей.

SELECT * FROM docs
WHERE to_tsvector('russian', body) @@ to_tsquery('russian', 'SQL & !Oracle');

-- Поиск "SQL" и не "Oracle" с использованием
-- русской морфологии.

Oracle: Оператор CONTAINS в контексте WHERE, требует индекс CONTEXT или CTXCAT. Синтаксис похож на MS SQL.

SELECT id FROM documents WHERE CONTAINS(text, '$(проект AND план)') > 0;

SQLite: Встроенной поддержки нет, но есть расширение FTS (Full-Text Search), которое создает виртуальные таблицы с поиском.

SELECT * FROM docs_fts WHERE docs_fts MATCH 'title: код OR body: программист';

Отличия от MS SQL: разная поддержка языков, синтаксис операторов, методы ранжирования и требования к индексам.

Распространенные ошибки

1. Отсутствие полнотекстового индекса. Функция работает только со столбцами, для которых создан такой индекс.

-- Ошибка, если индекс не создан
SELECT * FROM MyTable WHERE CONTAINS(PlainTextColumn, 'слово');

Сообщение об ошибке 7601:
"Cannot use a CONTAINS or FREETEXT predicate on table 'MyTable'
because it is not full-text indexed."

2. Использование для типов данных, не поддерживающих полнотекстовый индекс. Индекс можно создать только для столбцов типа char, varchar, nchar, nvarchar, text, ntext, image, xml, varbinary(max).

3. Неправильное экранирование специальных символов. Символы ", *, &, |, ~, !, ? в поисковом условии нужно экранировать двойными кавычками.

-- Неправильно: вызовет синтаксическую ошибку
SELECT * FROM t WHERE CONTAINS(c, 'C++');
-- Правильно:
SELECT * FROM t WHERE CONTAINS(c, '"C++"');

4. Игнорирование необходимости заполнения или обновления индекса. После вставки или обновления данных индекс нужно обновить (вручную или по расписанию). Поиск по новым данным может не сработать до обновления индекса.

Изменения в новых версиях SQL Server

В SQL Server 2012 и более поздних версиях существенных изменений синтаксиса функции CONTAINS не было. Основные улучшения касаются полнотекстового движка в целом.

1. Интеграция с семантическим поиском (начиная с SQL Server 2012). Позволяет находить документы, схожие по смыслу, используя статистику частоты терминов. Однако для этого применяются отдельные функции SEMANTICSIMILARITYTABLE, а не CONTAINS.

2. Улучшения производительности и управления полнотекстовыми каталогами. В современных версиях каталоги являются виртуальными объектами, не занимающими физическое файловое пространство, что упрощает администрирование.

3. Расширение языковой поддержки и словарей стоп-слов. Рекомендуется указывать параметр LANGUAGE для корректной работы морфологии и исключения стоп-слов конкретного языка.

Расширенные примеры и сценарии

Поиск с использованием тезауруса (синонимов). Требует настройки файла тезауруса для языка.

-- Найдет "автомобиль", "машина", "авто" если они
-- указаны как синонимы в тезаурусе русского языка.
SELECT * FROM Ads
WHERE CONTAINS(description, 'FORMSOF(THESAURUS, автомобиль)', LANGUAGE 1049);

Взвешенный поиск с помощью ISABOUT и WEIGHT.

SELECT * FROM ProductReviews
WHERE CONTAINS(review_text,
'ISABOUT("качество" WEIGHT(0.7), "цена" WEIGHT(0.3), "доставка" WEIGHT(0.5))'
);

-- Ищет слова с разными весами. Релевантность
-- вычисляется на основе этих весов при использовании
-- с CONTAINSTABLE.

Комбинирование разных условий в одном запросе.

SELECT id, title FROM Books
WHERE CONTAINS(
    (title, abstract, author),
    '("роман" NEAR "исторический") OR FORMSOF(INFLECTIONAL, издать)'
);

Поиск по всем индексированным столбцам таблицы.

SELECT * FROM Documents
WHERE CONTAINS(*, '"конфиденциально" AND "соглашение"');

Использование в сочетании с другими предикатами.

SELECT * FROM News
WHERE category_id = 5
AND CONTAINS(content, '"Microsoft" AND "Windows"')
AND publication_date > '2023-01-01';

Поиск фразы с указанием языка для обработки.

-- 1049 - код языка для русского
SELECT * FROM LegalActs
WHERE CONTAINS(full_text, '"договор аренды"', LANGUAGE 1049);