不同词序的模糊字符串匹配：SQL Server实现与算法选型

Great question! Let's break this down step by step, since your requirement is specifically about matching strings where word order doesn't matter, and partial word matches (like Tail matching ObfuscateTail) are allowed.

First: Is Levenshtein Distance a good fit? #

No, it's not ideal for your use case.

Levenshtein Distance measures the number of character-level edits (insertions, deletions, substitutions) needed to turn one string into another. It's highly dependent on the order of characters. For example, swapping the order of words like Apple 10mg/51L Tail and Tail 10mg/51L Apple would result in a low similarity score from Levenshtein, even though all the key components are present—because it doesn't recognize words as independent units. It also doesn't handle partial word matches naturally.

Recommended Algorithms for Your Scenario #

You need an approach that operates at the word level (not character level) and supports fuzzy/partial matches. Here are the best options, tailored for SQL Server:

1. Custom Word-Based Fuzzy Matching (Most Practical for SQL Server) #

This is the easiest to implement directly in SQL Server and aligns perfectly with your requirements:

• Split both the target and test strings into individual words (preserve complex tokens like 10mg/51L as single words).
• For each word in the target string, check if any word in the test string contains it (using LIKE '%[target_word]%').
• Calculate similarity as a percentage based on:
  • The number of target words that have a matching test word
  • Optional: Weight matches by how much of the test word overlaps with the target (e.g., Tail matching ObfuscateTail could count as ~28% instead of 100% for that word)

2. Modified Jaccard Similarity #

Jaccard similarity normally calculates the ratio of overlapping elements between two sets (intersection / union). For your use case:

• Treat each string as a set of words.
• Instead of exact matches, consider two words as overlapping if one partially matches the other (via LIKE).
• Similarity = (number of overlapping word pairs / total target words) * 100

3. TF-IDF + Cosine Similarity (For Large-Scale Data) #

If you're working with a large dataset and need more robust matching, this is a better choice:

• Convert each string into a vector where each element represents the importance (TF-IDF score) of a word.
• Use cosine similarity to measure how closely the two vectors align—this ignores word order and can be adjusted to include partial word matches by grouping related terms.
• Note: Implementing this natively in SQL Server requires more work (e.g., using full-text search or custom functions), but it's scalable.

SQL Server Implementation Example (Custom Word-Based Matching) #

Here's a simple, working function that calculates similarity based on partial word matches:

CREATE FUNCTION dbo.CalculateWordSimilarity(
    @TargetString NVARCHAR(MAX),
    @TestString NVARCHAR(MAX)
)
RETURNS DECIMAL(5,2)
AS
BEGIN
    -- Split strings into word tables (exclude empty values)
    DECLARE @TargetWords TABLE (Word NVARCHAR(100));
    DECLARE @TestWords TABLE (Word NVARCHAR(100));

    INSERT INTO @TargetWords
    SELECT value FROM STRING_SPLIT(@TargetString, ' ') WHERE TRIM(value) <> '';

    INSERT INTO @TestWords
    SELECT value FROM STRING_SPLIT(@TestString, ' ') WHERE TRIM(value) <> '';

    DECLARE @TotalTargetWords INT = (SELECT COUNT(*) FROM @TargetWords);
    IF @TotalTargetWords = 0 RETURN 0.00;

    -- Count how many target words have a partial match in test words
    DECLARE @MatchedCount INT = 0;
    SELECT @MatchedCount = COUNT(DISTINCT tw.Word)
    FROM @TargetWords tw
    INNER JOIN @TestWords tsw 
        ON tsw.Word LIKE '%' + tw.Word + '%';

    -- Return similarity percentage
    RETURN (@MatchedCount * 100.0) / @TotalTargetWords;
END

Test It Out: #

-- Returns 100.00 (all words match, regardless of order)
SELECT dbo.CalculateWordSimilarity('Apple 10mg/51L Tail', 'Tail 10mg/51L Apple') AS Similarity;

-- Returns 100.00 (all target words have partial matches in test string)
SELECT dbo.CalculateWordSimilarity('Apple 10mg/51L Tail', '51L MissleadingLENWord ObfuscateTail 10mg Apple') AS Similarity;

To Make It More Precise: #

You can adjust the function to weight matches by the overlap ratio. For example, instead of counting a match as 1, calculate LEN(tw.Word) / LEN(tsw.Word) and sum those values instead of counting matches. This gives a more granular similarity score for partial matches.

Final Takeaway #

Stick with a word-level fuzzy matching approach for SQL Server—it's straightforward to implement and directly addresses your requirement of ignoring word order and supporting partial matches. Levenshtein Distance is better suited for spelling corrections or character-level string alignment, not your specific use case.

内容的提问来源于stack exchange，提问作者Przemyslaw Remin