如何在SQL Server中结合Soundex与Contains实现多词搜索？

Absolutely, you can combine SOUNDEX with CONTAINS in SQL Server—though you need to be mindful of how each function works under the hood, since they serve different purposes. Let’s break this down step by step, including how to handle multi-word searches (2 words, 3 words, etc.).

First, a quick recap to set context:

• SOUNDEX() converts a string into a 4-character code based on its pronunciation, so words that sound alike (like "Smith" and "Smyth") get the same code.
• CONTAINS() is a full-text search function that lets you search for words/phrases efficiently, but it requires a full-text index on the target column.

Since CONTAINS relies on full-text indexed data, you have two main approaches to combine it with SOUNDEX:

Approach 1: Precompute SOUNDEX Values (Most Efficient) #

The fastest way is to store precomputed SOUNDEX codes in a dedicated column, then create a full-text index on that column. This lets you use CONTAINS to search for SOUNDEX codes directly, avoiding on-the-fly calculations for every row.

Example Setup #

1. Add a persisted computed column to your table to store SOUNDEX values:

ALTER TABLE Contacts
ADD FullNameSoundex AS SOUNDEX(FullName) PERSISTED;

2. Create a full-text catalog and index (skip if you already have one set up):

CREATE FULLTEXT CATALOG ft_Contacts_Catalog AS DEFAULT;
CREATE FULLTEXT INDEX ON Contacts(FullNameSoundex)
KEY INDEX PK_Contacts; -- Replace with your table's primary key index name

Multi-Word Search with This Approach #

To search for records where the name sounds like both "John" and "Doe" (two words), compute the SOUNDEX codes for each term and pass them to CONTAINS:

DECLARE @SoundexJohn VARCHAR(4) = SOUNDEX('John');
DECLARE @SoundexDoe VARCHAR(4) = SOUNDEX('Doe');

-- Match both SOUNDEX codes
SELECT *
FROM Contacts
WHERE CONTAINS(FullNameSoundex, '"' + @SoundexJohn + '" AND "' + @SoundexDoe + '"');

For three words (e.g., "John Michael Doe"), extend the pattern with an additional SOUNDEX code:

DECLARE @SoundexJohn VARCHAR(4) = SOUNDEX('John');
DECLARE @SoundexMichael VARCHAR(4) = SOUNDEX('Michael');
DECLARE @SoundexDoe VARCHAR(4) = SOUNDEX('Doe');

SELECT *
FROM Contacts
WHERE CONTAINS(FullNameSoundex, '"' + @SoundexJohn + '" AND "' + @SoundexMichael + '" AND "' + @SoundexDoe + '"');

Approach 2: Dynamic Combination (No Schema Changes) #

If you can’t modify your table structure, you can combine SOUNDEX with CONTAINS using CTEs or subqueries. Note that this is less efficient for large tables, as it calculates SOUNDEX for every row.

Example for Two Words #

Suppose you want to search for contacts with names sounding like "John Doe" who also have "Engineer" in their job title (using CONTAINS for the job title filter):

DECLARE @FirstNameSearch VARCHAR(50) = 'John';
DECLARE @LastNameSearch VARCHAR(50) = 'Doe';

WITH FilteredContacts AS (
    SELECT *
    FROM Contacts
    WHERE CONTAINS(JobTitle, 'Engineer') -- Use full-text first to reduce row count
)
SELECT *
FROM FilteredContacts
WHERE SOUNDEX(SUBSTRING(FullName, 1, CHARINDEX(' ', FullName)-1)) = SOUNDEX(@FirstNameSearch)
  AND SOUNDEX(SUBSTRING(FullName, CHARINDEX(' ', FullName)+1, LEN(FullName))) = SOUNDEX(@LastNameSearch);

For Three Words #

Adjust the substring logic to split the full name into three parts (first, middle, last):

DECLARE @FirstNameSearch VARCHAR(50) = 'John';
DECLARE @MiddleNameSearch VARCHAR(50) = 'Michael';
DECLARE @LastNameSearch VARCHAR(50) = 'Doe';

WITH SplitNames AS (
    SELECT 
        FullName,
        SUBSTRING(FullName, 1, CHARINDEX(' ', FullName)-1) AS FirstName,
        SUBSTRING(FullName, CHARINDEX(' ', FullName)+1, CHARINDEX(' ', FullName, CHARINDEX(' ', FullName)+1)-CHARINDEX(' ', FullName)-1) AS MiddleName,
        SUBSTRING(FullName, CHARINDEX(' ', FullName, CHARINDEX(' ', FullName)+1)+1, LEN(FullName)) AS LastName
    FROM Contacts
)
SELECT *
FROM SplitNames
WHERE SOUNDEX(FirstName) = SOUNDEX(@FirstNameSearch)
  AND SOUNDEX(MiddleName) = SOUNDEX(@MiddleNameSearch)
  AND SOUNDEX(LastName) = SOUNDEX(@LastNameSearch);

Important Notes #

• SOUNDEX works best for English words; for other languages, consider DIFFERENCE() (which returns a similarity score between SOUNDEX codes) instead.
• Full-text indexes need regular rebuilds if your data changes frequently to keep search results accurate.
• For dynamic multi-word searches where word positions aren’t fixed, use a table-valued function to split your input string into individual words, compute their SOUNDEX codes, then check each code against the target column.

内容的提问来源于stack exchange，提问作者Ali.K