How to Make a Search Query That Contains Each Word of a String

Searching text efficiently is a common requirement in SQL Server–based applications. One frequent challenge developers face is building a SQL Server search query that matches all words in a string, not just one of them.

In this article, you’ll learn multiple ways to search for rows that contain every word in a search string in SQL Server, including dynamic queries, performance-friendly patterns, and full-text search solutions.

Why Search for Each Word in SQL Server?

In many real-world scenarios—such as search boxes, filters, and document lookups—you want results that include all keywords entered by the user.

For example, if the search input is:

sql server performance

The query should return only rows that contain sql, server, and performance, in any order.

Sample Table and Search Input

CREATE TABLE Articles (
    ArticleId INT PRIMARY KEY,
    Title NVARCHAR(200),
    Content NVARCHAR(MAX)
);

DECLARE @SearchText NVARCHAR(200) = 'sql server performance';

Method 1: Using STRING_SPLIT to Match All Words (Recommended)

The most reliable and dynamic solution is using STRING_SPLIT with a NOT EXISTS condition.

SQL Query: Search for Each Word in a String

SELECT a.*
FROM Articles a
WHERE NOT EXISTS (
    SELECT 1
    FROM STRING_SPLIT(@SearchText, ' ') s
    WHERE a.Content NOT LIKE '%' + s.value + '%'
);

Why This Works

STRING_SPLIT breaks the search string into individual words.

NOT EXISTS ensures no word is missing.

The query only returns rows containing all words.

✅ Best general-purpose solution.
✅ Works with any number of keywords.
❌ Uses LIKE ‘%word%’, which may affect performance on large tables.

Method 2: Count-Based Keyword Matching in SQL Server

Another way to ensure all words exist is by counting matches.

SELECT a.*
FROM Articles a
CROSS APPLY (
    SELECT COUNT(*) AS TotalWords
    FROM STRING_SPLIT(@SearchText, ' ')
) t
CROSS APPLY (
    SELECT COUNT(*) AS MatchedWords
    FROM STRING_SPLIT(@SearchText, ' ') s
    WHERE a.Content LIKE '%' + s.value + '%'
) m
WHERE m.MatchedWords = t.TotalWords;

Pros

Clear logic.
Easy to debug.

Cons

Slightly slower than NOT EXISTS.
More verbose SQL.

Method 3: Using Multiple LIKE Conditions (Not Scalable)

SELECT *
FROM Articles
WHERE Content LIKE '%sql%'
  AND Content LIKE '%server%'
  AND Content LIKE '%performance%';

🚫 Not dynamic.
🚫 Hard to maintain.
🚫 Poor performance for long search strings.

This approach is only suitable for fixed or very small queries.

Case-Sensitive vs Case-Insensitive Search in SQL Server

SQL Server search behavior depends on collation.

Case-Insensitive Search

WHERE a.Content COLLATE Latin1_General_CI_AS
      LIKE '%' + s.value + '%';

Case-Sensitive Search

WHERE a.Content COLLATE Latin1_General_CS_AS
      LIKE '%' + s.value + '%';

Performance Considerations

Using LIKE ‘%word%’:
Prevents index usage.
Slows down queries on large datasets.
Is not ideal for high-traffic applications.

Optimization Tips

Limit the number of search terms.
Avoid searching large NVARCHAR(MAX) columns where possible.
Use Full-Text Search for production systems.

Best Performance Solution: SQL Server Full-Text Search

For large databases, Full-Text Search is the most efficient way to search multiple keywords.

Example Full-Text Query

SELECT *
FROM Articles
WHERE CONTAINS(Content, '"sql" AND "server" AND "performance"');

Advantages

Much faster than LIKE.
Supports word forms and ranking.
Designed specifically for text searching.

Disadvantages

Requires Full-Text Index setup.
Not available on all editions.

Best Practice Summary

Small to medium data uses STRING_SPLIT + NOT EXISTS.
Dynamic keyword search uses STRING_SPLIT.
High-performance systems uses Full-Text Search.
Fixed keywords uses Multiple LIKE.

Final Recommendation

Best SQL Server Query to Search Each Word in a String

WHERE NOT EXISTS (
    SELECT 1
    FROM STRING_SPLIT(@SearchText, ' ') s
    WHERE ColumnName NOT LIKE '%' + s.value + '%'
);

To ensure that a SQL Server query matches each word in a search string:

Use STRING_SPLIT with NOT EXISTS for clean and dynamic logic.
Avoid hardcoded LIKE conditions.
Consider Full-Text Search for large datasets or high-performance needs.