SQL CONTAINS: Syntax, Usage, and Examples

The CONTAINS keyword is used to perform full-text search queries in databases that support this feature. Unlike LIKE, which performs pattern matching using wildcard characters, CONTAINS enables advanced searches that check if specific words or phrases exist within a column. This makes CONTAINS particularly valuable for querying large text fields, such as product descriptions, news articles, or blog content.

This guide walks through how CONTAINS works, its syntax, supported databases, practical examples, and common alternatives when CONTAINS isn't available.

You’ll also see how to use it effectively for optimization and performance tuning.

What Is CONTAINS in SQL?

The CONTAINS keyword allows you to check whether a specific word or phrase exists in a column that has full-text indexing enabled. It is used primarily in SQL Server and Oracle. Unlike basic string comparisons, CONTAINS supports natural language searches and can evaluate multiple keywords, phrases, and operators like AND, OR, and NEAR.

Because CONTAINS works with full-text indexing, it’s often part of a predicate in the WHERE clause that filters based on relevance rather than exact matches.

The CONTAINS clause only works on columns that are indexed for full-text search. If a column isn't indexed, attempting to use CONTAINS will result in an error.

Syntax of CONTAINS

Here is the basic syntax for CONTAINS:

SELECT column1, column2
FROM table_name
WHERE CONTAINS(column_name, 'search_text');

You can use phrases, boolean operators, and wildcards to perform more complex searches. Examples include:

-- Search for exact word
WHERE CONTAINS(description, 'laptop');

-- Search for any of multiple words
WHERE CONTAINS(description, 'laptop OR tablet');

-- Search for words near each other
WHERE CONTAINS(description, 'NEAR(laptop, tablet)');

These options make CONTAINS more flexible than standard LIKE or equality conditions.

SQL CONTAINS Example: Basic Query

Suppose you have a Products table with a description column. You can use CONTAINS to find rows that contain the word "wireless":

SELECT name, description
FROM Products
WHERE CONTAINS(description, 'wireless');

This will return all products whose descriptions contain the term "wireless," assuming the description column has full-text indexing.

If your search involves unicode or special XML content, ensure the column supports full-text parsing of those encodings.

Advanced CONTAINS SQL Examples

Searching for Phrases

SELECT title
FROM Articles
WHERE CONTAINS(content, '"machine learning"');

This query finds articles that contain the exact phrase “machine learning.”

Using Boolean Operators

SELECT title
FROM Articles
WHERE CONTAINS(content, 'blockchain AND finance');

This finds rows where both terms are present in the same column.

Using NEAR Operator

SELECT title
FROM Articles
WHERE CONTAINS(content, 'NEAR(climate, change)');

This retrieves results where the terms “climate” and “change” appear near each other, useful for more contextually relevant matches.

Combining CONTAINS with Other Functions

You can combine CONTAINS with built-in functions like LEN() or charindex for additional flexibility. For instance:

SELECT title
FROM Articles
WHERE CONTAINS(content, 'data') AND CHARINDEX('AI', content) > 0;

This hybrid approach allows both indexed full-text searches and targeted substring detection.

A varchar column is often used for storing text efficiently, making it ideal for these searches.

How CONTAINS SQL Differs from LIKE

While both CONTAINS and LIKE are used to filter string values, they behave very differently.

The LIKE operator checks for patterns using % and _ wildcards. It doesn't understand word boundaries or context.

-- Using LIKE
SELECT * FROM Products WHERE description LIKE '%wireless%';

This will find any description containing “wireless,” but also "wirelessness" or "ultrawireless," which may not be intended.

In contrast, CONTAINS searches indexed tokens and understands word boundaries, giving more relevant results for word-based queries.

CONTAINS also performs better for large datasets and complex searches when full-text indexing is used.

Limitations of CONTAINS

Despite its power, CONTAINS has a few limitations:

• Only supported by databases like SQL Server and Oracle with full-text search enabled.
• Columns must be indexed with full-text indexing.
• Not supported in MySQL and PostgreSQL using default configurations.
• Cannot be used with non-text data types.
• Requires special configuration and may increase storage or indexing overhead.

In databases where CONTAINS is not supported, alternatives like LIKE, regular expressions, or full-text extensions must be used instead.

CONTAINS in Unsupported Systems

In MySQL, there is no native CONTAINS keyword. Instead, FULLTEXT indexes are used with MATCH ... AGAINST syntax:

SELECT *
FROM articles
WHERE MATCH(content) AGAINST('data science');

In PostgreSQL, to_tsvector and to_tsquery can be used for similar purposes:

SELECT *
FROM articles
WHERE to_tsvector('english', content) @@ to_tsquery('data & science');

These approaches replicate the functionality of CONTAINS in systems that don’t support the keyword directly.

CONTAINS alternatives

If CONTAINS is not available in your environment, and full-text search is not enabled, you can fall back on LIKE for basic pattern matching:

SELECT *
FROM Products
WHERE description LIKE '%wireless%';

This is a simple but less precise alternative to CONTAINS using LIKE.

You can also combine LIKE with LOWER() or UPPER() to ensure case-insensitive matching in some SQL dialects. You can also include them in stored procedures for reusable search logic.

SQL Command for CONTAINS Setup

Before using CONTAINS, ensure that the column you're querying is full-text indexed. In SQL Server, you can set this up as follows:

-- Create a full-text catalog
CREATE FULLTEXT CATALOG ProductCatalog;

-- Create a full-text index
CREATE FULLTEXT INDEX ON Products(description)
KEY INDEX PK_Products
ON ProductCatalog;

Once the index is created and populated, CONTAINS can be used in your queries.

Without this step, the query will fail with an error about missing full-text indexes.

Using CONTAINS in Table Creation

To prepare a table for full-text search, you must set up full-text indexing from the start. For example:

CREATE TABLE Articles (
    id INT PRIMARY KEY,
    title VARCHAR(255),
    content NVARCHAR(MAX)
);

After using CREATE TABLE, enable full-text indexing on the content column.

SQL Query for Multiple Columns and Search Terms

You can search across multiple columns or different words at once:

SELECT *
FROM Products
WHERE CONTAINS((name, description), 'smartphone OR tablet');

Here, both columns are searched for the search term. This is a contains query that checks for text across several fields.

Practical Use Cases for CONTAINS

Here are a few real-world examples of how CONTAINS can be applied:

• In an e-commerce system: Search for products by keyword across titles, descriptions, and tags.
• In a document management system: Search legal documents for exact phrases.
• In a news portal: Retrieve articles where a term appears in the body of the text or summary.
• In a helpdesk app: Match tickets that mention error codes or issue types in the problem description.
• In a knowledge base: Allow users to search using natural language queries.

The precision and flexibility of CONTAINS make it ideal for all these scenarios.

Best Practices for Using CONTAINS

• Index only columns that require full-text search to save space.
• Avoid using CONTAINS on high-update tables unless indexing performance is optimized.
• Use phrase searching when necessary by wrapping in double quotes.
• Avoid overusing OR in large search expressions—it can slow down performance.
• When using multiple columns, wrap them in parentheses.
• Sanitize input to avoid injection risks, especially in dynamic SQL queries using CONTAINS.