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.

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.

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.

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.

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.

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.

SQL Query for String CONTAINS with Multiple Columns

To check for keywords across more than one column, you can pass multiple columns into the CONTAINS clause:

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

This matches rows where the keyword appears in either the name or description fields.

This is especially helpful in search forms, autocomplete systems, and large document-based applications.

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.