尋找模糊弦函數的近似匹配

12 分鐘

小提示

有關更多詳細信息，請參閱 文本和圖像 選項卡！

真實世界的數據很少能完全吻合。顧客名稱可能拼錯、地址縮寫不同，或產品描述輸入不一致。模糊字串匹配功能能幫助你找到相似但不完全相同的紀錄，從而提升資料品質、重複偵測及更靈活的搜尋功能。

了解字串相似度的概念

模糊匹配演算法透過計算兩個字串之間的差異來測量它們的相似度。目前有兩種主要方法被廣泛使用：

編輯距離（Levenshtein 距離） 計算將一個字串轉換成另一個字串所需的最少單字元操作次數（插入、刪除、替換）。數值越低表示弦數較相似。

相似度分數 以百分比或比率表示字串間的關係，數值越高表示相似度越高。

請考慮下列範例：

「color」→「colour」：編輯距離 = 1（插入 'u'）
"database" → "databaes": edit distance = 2 (swap 'e' and 's')
「Microsoft」→「Microsft」：編輯距離 = 1（刪除 'o'）

備註

模糊匹配比對計算成本高於精確匹配。策略性地使用它，通常用於已依據其他條件預先篩選過的候選集。

計算編輯距離 `EDIT_DISTANCE`

該 EDIT_DISTANCE 函數回傳兩字串間的 Levenshtein 距離，這是將一個字串轉換為另一字串所需的最少編輯次數。目標是根據定義的閾值找到相似的字串。

以下範例示範如何使用 EDIT_DISTANCE：

SELECT 
    EDIT_DISTANCE('color', 'colour') AS ColorVariant,     -- Returns 1
    EDIT_DISTANCE('database', 'databaes') AS Typo,        -- Returns 2
    EDIT_DISTANCE('SQL Server', 'SQL Server') AS Exact,   -- Returns 0
    EDIT_DISTANCE('hello', 'world') AS Different;         -- Returns 4

你可以用 EDIT_DISTANCE 來查找即使有些微差異，也可能重複或相符的紀錄：

-- Find customers with similar names to a search term
DECLARE @searchName NVARCHAR(100) = 'Jon Smith';

SELECT 
    CustomerID,
    FirstName,
    LastName,
    FirstName + ' ' + LastName AS FullName,
    EDIT_DISTANCE(@searchName, FirstName + ' ' + LastName) AS EditDistance
FROM SalesLT.Customer
WHERE EDIT_DISTANCE(@searchName, FirstName + ' ' + LastName) <= 3
ORDER BY EDIT_DISTANCE(@searchName, FirstName + ' ' + LastName);

此外，你也可以找到可能重複的產品：

-- Find product pairs with similar names
SELECT 
    p1.ProductID AS Product1ID,
    p1.Name AS Product1Name,
    p2.ProductID AS Product2ID,
    p2.Name AS Product2Name,
    EDIT_DISTANCE(p1.Name, p2.Name) AS EditDistance
FROM SalesLT.Product AS p1
INNER JOIN SalesLT.Product AS p2
    ON p1.ProductID < p2.ProductID
WHERE EDIT_DISTANCE(p1.Name, p2.Name) <= 5
ORDER BY EDIT_DISTANCE(p1.Name, p2.Name);

小提示

最大有意義的編輯距離取決於字串長度。對於短字串（5-10 個字元），編輯距離為 1-2 表示相似度。如果弦比較長，你可以允許距離在3到5之間。

衡量與`EDIT_DISTANCE_SIMILARITY`的相似度

EDIT_DISTANCE_SIMILARITY 回傳 0 到 100 之間的標準化相似度分數，其中 100 代表相同的字串。這種基於百分比的指標比原始編輯距離更容易解讀，尤其是在比較不同長度字串時：

SELECT 
    EDIT_DISTANCE_SIMILARITY('color', 'colour') AS ColorSimilarity,     -- ~85
    EDIT_DISTANCE_SIMILARITY('database', 'databaes') AS TypoSimilarity, -- ~75
    EDIT_DISTANCE_SIMILARITY('SQL', 'SQL Server') AS PartialMatch,      -- ~30
    EDIT_DISTANCE_SIMILARITY('hello', 'hello') AS Exact;                -- 100

你可以利用相似度分數尋找具有閾值的近似匹配，如下範例：

-- Find products similar to a search term (at least 70% similar)
DECLARE @searchTerm NVARCHAR(100) = 'Mountain Bike Frame';

SELECT 
    ProductID,
    Name,
    EDIT_DISTANCE_SIMILARITY(@searchTerm, Name) AS SimilarityScore
FROM SalesLT.Product
WHERE EDIT_DISTANCE_SIMILARITY(@searchTerm, Name) >= 70
ORDER BY EDIT_DISTANCE_SIMILARITY(@searchTerm, Name) DESC;

使用`JARO_WINKLER_DISTANCE`計算語音相似度

Jaro-Winkler 演算法設計用於比較名稱與短字串。它會給從一開始就匹配的字串較高分數，使得對於前綴較為重要的人名來說非常有效：

SELECT 
    JARO_WINKLER_DISTANCE('MARTHA', 'MARHTA') AS NameTypo,      -- ~0.96
    JARO_WINKLER_DISTANCE('JONES', 'JOHNSON') AS SimilarNames,  -- ~0.83
    JARO_WINKLER_DISTANCE('JOHN', 'JON') AS NameVariant,        -- ~0.93
    JARO_WINKLER_DISTANCE('SMITH', 'SMYTH') AS SpellingVar;     -- ~0.96

Jaro-Winkler 分數範圍為0到1,1代表相同的字串。分數高於0.9通常表示名字匹配度強。

以下範例是尋找與搜尋輸入相似名字的客戶：

-- Find customers with names similar to a search
DECLARE @searchFirst NVARCHAR(50) = 'John';
DECLARE @searchLast NVARCHAR(50) = 'Smythe';

SELECT 
    CustomerID,
    FirstName,
    LastName,
    JARO_WINKLER_DISTANCE(@searchFirst, FirstName) AS FirstNameScore,
    JARO_WINKLER_DISTANCE(@searchLast, LastName) AS LastNameScore,
    (JARO_WINKLER_DISTANCE(@searchFirst, FirstName) + 
     JARO_WINKLER_DISTANCE(@searchLast, LastName)) / 2 AS CombinedScore
FROM SalesLT.Customer
WHERE JARO_WINKLER_DISTANCE(@searchFirst, FirstName) > 0.85
  AND JARO_WINKLER_DISTANCE(@searchLast, LastName) > 0.85
ORDER BY CombinedScore DESC;

備註

Jaro-Winkler 是針對短字串如名字進行優化的。對於較長的字串，如地址或描述， EDIT_DISTANCE_SIMILARITY 通常會有更好的結果。

效能考量

模糊匹配函數會檢查兩個字串中的每個字元，因此計算量極高。精確字串比較可在字元差異後立即停止，索引查找則使用高效的 B 樹遍歷。相較之下，模糊演算法必須逐字元計算相似度分數。對於一個有 1,000,000 列的資料表，未優化的模糊搜尋可能會進行 1,000,000 次相似度計算，每次都涉及數十次字元比較。

高效模糊匹配的關鍵是在應用昂貴的模糊函數前先減少候選集。使用具有 LIKE 模式的索引欄位、相關欄位的精確匹配或範圍篩選器來首先縮小結果範圍。只有在這時，才對較小的候選集合套用模糊匹配。

以下範例展示了這種漸進式過濾方法：

-- Not good: Fuzzy match against entire table
SELECT * FROM LargeCustomerTable
WHERE EDIT_DISTANCE_SIMILARITY('John Smith', FullName) > 70;

-- Better: Pre-filter before fuzzy matching
SELECT * FROM LargeCustomerTable
WHERE FullName LIKE 'J%'  -- First letter filter
  AND EDIT_DISTANCE_SIMILARITY('John Smith', FullName) > 70;

-- Best: Use multiple pre-filters
SELECT * FROM LargeCustomerTable
WHERE FirstName LIKE 'Jo%'
  AND LastName LIKE 'Sm%'
  AND JARO_WINKLER_DISTANCE('John', FirstName) > 0.85
  AND JARO_WINKLER_DISTANCE('Smith', LastName) > 0.85;

這很重要

像 EDIT_DISTANCE、 EDIT_DISTANCE_SIMILARITY、 JARO_WINKLER_DISTANCE 和這類模糊字串匹配函數可在 SQL Server 2025 及以後版本、Azure SQL 資料庫，以及 Microsoft Fabric 中的 SQL 資料庫中使用。請查看你平台的文件以了解具體功能可用性。

欲了解更多模糊字串匹配的資訊，請參見字串函數。

意見反應

此頁面對您有幫助嗎？