The CONTAINS
function in SQL is used for full-text search to determine if a specified column contains a specified string or phrase. It is particularly useful for searching large text fields for specific words or phrases.
Full-Text Search
The CONTAINS
function is available in SQL Server and other databases that support full-text indexing. This function allows for more advanced search capabilities compared to the basic LIKE
operator.
Syntax
SELECT column1, column2, ...
FROM table_name
WHERE CONTAINS(column_name, 'search_term');
Example
Consider a table named documents
with columns id
, title
, and content
.
CREATE TABLE documents (
id INT,
title VARCHAR(100),
content TEXT
);
INSERT INTO documents (id, title, content)
VALUES
(1, 'SQL Full-Text Search', 'This is a tutorial on SQL full-text search.'),
(2, 'Introduction to SQL', 'SQL stands for Structured Query Language.'),
(3, 'Advanced SQL Techniques', 'This document covers advanced SQL techniques.');
To search for documents that contain the word "SQL" in the content
column:
SELECT id, title
FROM documents
WHERE CONTAINS(content, 'SQL');
This query will return all documents where the content
contains the word "SQL".
Setting Up Full-Text Search in SQL Server
Before using the CONTAINS
function, you need to set up full-text indexing on the table and columns you want to search.
Create a Full-Text Catalog:
CREATE FULLTEXT CATALOG ftCatalog AS DEFAULT;
Create a Full-Text Index:
CREATE FULLTEXT INDEX ON documents(content)
KEY INDEX PK_documents;
Populate the Full-Text Index:
ALTER FULLTEXT INDEX ON documents START FULL POPULATION;
Using LIKE
for Basic Searches
If full-text search is not available or necessary, you can use the LIKE
operator for basic pattern matching.
SELECT column1, column2, ...
FROM table_name
WHERE column_name LIKE '%search_term%';
Example with LIKE
SELECT id, title
FROM documents
WHERE content LIKE '%SQL%';
This query returns documents where the content
contains the substring "SQL".
The CONTAINS
function in SQL provides powerful full-text search capabilities, allowing you to efficiently search large text fields for specific words or phrases. While setting up full-text indexing requires some initial effort, the resulting search performance and flexibility can significantly enhance your ability to query textual data. For simpler scenarios or databases without full-text search support, the LIKE
operator remains a useful tool for basic pattern matching.