SQL Server如何返回含指定字符串前缀的列值？CONTAINS查询未达预期

Ah, I see the issue here! You're trying to pull all words that are prefixes of "absolutely" (including the word itself), but CONTAINS isn't built for that kind of pattern matching. Let's break this down and fix it.

Why Your Current Query Fails #

The CONTAINS function in SQL Server is designed for full-text search operations—it looks for exact word matches, synonyms, or inflected forms (like plurals). When you run CONTAINS(search, 'absolutely'), it only returns rows where the search column has the exact word "absolutely" (or related inflections if your full-text index is set up for that). It doesn't check if other words are prefixes of "absolutely".

The Correct Approach #

To get all words that are prefixes of "absolutely" (so "ab", "absol", "absolute", "absolutely"), you can use the LIKE operator in reverse. Instead of checking if the word contains "absolutely", check if "absolutely" starts with the word from your table:

SELECT word 
FROM [dbo].[SentimentModel1] 
WHERE 'absolutely' LIKE word + '%';

Here's why this works: #

• For each word in your table, we append a wildcard (%) to it, which means "any sequence of characters can follow".
• The condition 'absolutely' LIKE word + '%' will be true if "absolutely" starts with that word. This perfectly captures all prefixes of "absolutely" plus the word itself.

If You Needed the Opposite (Words Starting With Prefixes of "absolutely") #

Just in case you meant to get words that start with any prefix of "absolutely" (like "abacus", "absolute", "absolutely", etc.), you could use a series of LIKE conditions:

SELECT word 
FROM [dbo].[SentimentModel1] 
WHERE word LIKE 'ab%' 
   OR word LIKE 'absol%' 
   OR word LIKE 'absolute%' 
   OR word LIKE 'absolutely%';

But based on your example, the first query is exactly what you're looking for.

Content of the question comes from Stack Exchange, question author PNC