あいまい文字列関数を使用して近似一致を検索する

完了済み

ヒント

詳細については、「 テキストと画像 」タブを参照してください。

実際のデータが完全に一致することはめったにありません。 顧客名のスペルが間違っているか、住所の省略方法が異なるか、製品の説明が一貫していない可能性があります。 あいまい文字列一致関数は、類似しているが同一ではないレコードを検索するのに役立ち、データ品質の向上、重複検出、およびより柔軟な検索機能を実現します。

文字列の類似性の概念を理解する

あいまい一致アルゴリズムでは、2 つの文字列の違いを計算することによって、2 つの文字列の類似性が測定されます。 一般的に、次の 2 つの主な方法が使用されます。

編集距離 (Levenshtein 距離) は、1 つの文字列を別の文字列に変換するために必要な 1 文字の操作 (挿入、削除、置換) の最小数をカウントします。 値が小さいほど、より類似した文字列が示されます。

類似性スコアは 、文字列間の関係をパーセンテージまたは比率として表します。値が高いほど類似性が高いことを示します。

次のような例を考えてみてください。

  • "color" → "color": 編集距離 = 1 ('u' を挿入)
  • ja-JP: 「データベース」→「データバス」: 編集距離 = 2 ('e' と 's' の入れ替え)
  • "Microsoft" → "Microsft": 編集距離 = 1 ('o' を削除)

注

あいまい一致は、正確な一致と比較して計算コストが高くなります。 戦略的に使用します。通常は、他の条件を使用して事前フィルター処理される候補セットに対して使用します。

編集距離をEDIT_DISTANCEで計算する

EDIT_DISTANCE関数は、2 つの文字列間の Levenshtein 距離を返します。これは、1 つの文字列を他方の文字列に変換するために必要な編集の最小数です。 目標は、定義されたしきい値に基づいて類似する文字列を見つけることです。

次の例では、 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;

Important

EDIT_DISTANCE、EDIT_DISTANCE_SIMILARITY、JARO_WINKLER_DISTANCEなどのあいまい文字列一致関数は、SQL Server 2025 以降、Azure SQL Database、および Microsoft Fabric の SQL データベースで使用できます。 特定の機能の可用性については、プラットフォームのドキュメントを確認してください。

あいまい文字列の一致の詳細については、「 文字列関数」を参照してください。