Finn omtrentlige treff med fuzzy strengfunksjoner

12 minutter

Virkelige data stemmer sjelden helt overens. Kundenavn kan være feilstavet, adresser forkortet annerledes, eller produktbeskrivelser skrevet inn inkonsekvent. Fuzzy-strengmatchingsfunksjoner hjelper deg å finne poster som er like, men ikke identiske, noe som muliggjør forbedringer av datakvalitet, duplikatdeteksjon og mer fleksible søkemuligheter.

Forstå konsepter for strenglikhet

Fuzzy matching-algoritmer måler hvor like to strenger er ved å beregne forskjellene mellom dem. To hovedtilnærminger brukes vanligvis:

Redigeringsavstand (Levenshtein-avstand) teller det minste antallet enkelttegnsoperasjoner (innsettinger, slettinger, substitusjoner) som trengs for å transformere én streng til en annen. Lavere verdier indikerer flere lignende strenger.

Likhetsscore uttrykker forholdet mellom strengene som en prosentandel eller forhold, der høyere verdier indikerer større likhet.

Vurder disse eksemplene:

"color" → "colour": redigeringsavstand = 1 (sett inn 'u')
"database" → "databaes": redigeringsavstand = 2 (bytt 'e' og 's')
"Microsoft" → "Microsft": redigeringsavstand = 1 (slett 'o')

Bemerkning

Fuzzy matching er beregningsmessig kostbart sammenlignet med eksakt matching. Bruk det strategisk, vanligvis på kandidatsett som er forhåndsfiltrert med andre kriterier.

Beregn redigeringsavstand med `EDIT_DISTANCE`

Funksjonen EDIT_DISTANCE returnerer Levenshtein-avstanden mellom to strenger, som er det minste antallet redigeringer som kreves for å transformere én streng til en annen. Målet er å finne strenger som er like basert på en definert terskel.

Følgende eksempel demonstrerer hvordan man bruker 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

Du kan bruke EDIT_DISTANCE dem til å finne poster som kan være duplikater eller treff til tross for små variasjoner:

-- 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);

Du kan også finne potensielle duplikatprodukter:

-- 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);

Tips

Den maksimale meningsfulle redigeringsavstanden avhenger av strenglengden. For korte strenger (5-10 tegn) indikerer en redigeringsavstand på 1-2 likhet. For lengre strenger kan du tillate avstander på 3-5.

Mål likhet med `EDIT_DISTANCE_SIMILARITY`

EDIT_DISTANCE_SIMILARITY returnerer en normalisert likhetsscore mellom 0 og 100, hvor 100 representerer identiske strenger. Denne prosentbaserte metrikken er lettere å tolke enn rå redigeringsavstand, spesielt når man sammenligner strenger av ulik lengde:

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

Du kan bruke likhetspoeng for å finne omtrentlige treff med en terskel som i følgende eksempel:

-- 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;

Beregn fonetisk likhet med `JARO_WINKLER_DISTANCE`

Jaro-Winkler-algoritmen er spesielt utviklet for å sammenligne navn og korte strenger. Den gir høyere poengsum til strenger som matcher fra starten, noe som gjør den spesielt effektiv for personnavn hvor prefikser er viktigere:

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-poengsummen varierer fra 0 til 1, hvor 1 indikerer identiske strenger. En score over 0,9 indikerer vanligvis en sterk match for navn.

Følgende eksempel finner kunder med navn som ligner en søkeinput:

-- 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;

Bemerkning

Jaro-Winkler er optimalisert for korte strenger som navn. For lengre strenger som adresser eller beskrivelser EDIT_DISTANCE_SIMILARITY gir ofte bedre resultater.

Ytelseshensyn

Fuzzy matching-funksjoner undersøker hvert tegn i begge strengene, noe som gjør dem beregningsmessig krevende. Nøyaktig strengsammenligning kan stoppe så snart tegn skiller seg, og indekserte oppslag bruker effektiv gjennomgang av B-tre. I kontrast må fuzzy-algoritmer beregne likhetspoeng tegn for tegn. For en tabell med én million rader kan et uoptimalisert fuzzy-søk utføre én million likhetsberegninger, hver med dusinvis av tegnsammenligninger.

Nøkkelen til effektiv fuzzy matching er å redusere kandidatmengden før de kostbare fuzzy-funksjonene tas i bruk. Bruk indekserte kolonner med LIKE mønstre, nøyaktige treff på relaterte felt, eller områdefiltre for å snevre inn resultatene først. Først deretter kan du anvende fuzzy matching på det mindre kandidatsettet.

Følgende eksempler viser denne progressive filtreringsmetoden:

-- 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;

Viktig!

Fuzzy-strengmatchingsfunksjoner som EDIT_DISTANCE, EDIT_DISTANCE_SIMILARITY, og JARO_WINKLER_DISTANCE er tilgjengelige i SQL Server 2025 og nyere, Azure SQL Database, og SQL-databaser i Microsoft Fabric. Sjekk plattformens dokumentasjon for spesifikke funksjonsmuligheter.

For mer informasjon om fuzzy string matching, se String Functions.

Tilbakemeldinger

Var denne siden nyttig?