Búsqueda de coincidencias aproximadas con funciones de cadenas difusas
Los datos reales rara vez coinciden perfectamente. Los nombres de los clientes pueden escribirse mal, las direcciones abreviadas de forma diferente o las descripciones de productos escritas incoherentemente. Las funciones de coincidencia de cadenas aproximadas ayudan a encontrar registros similares, pero no idénticos, lo que permite mejoras de calidad de los datos, detección de duplicados y funcionalidades de búsqueda más flexibles.
Comprenda los conceptos de similitud de cadenas
Los algoritmos de coincidencia aproximada miden la similitud de dos cadenas mediante el cálculo de las diferencias entre ellas. Normalmente se usan dos enfoques principales:
La distancia de edición (distancia levenshtein) cuenta el número mínimo de operaciones de un solo carácter (inserciones, eliminaciones, sustituciones) necesarias para transformar una cadena en otra. Los valores inferiores indican cadenas más similares.
Las puntuaciones de similitud expresan la relación entre cadenas como porcentaje o proporción, donde los valores más altos indican una mayor similitud.
Tenga en cuenta estos ejemplos:
- "color" → "color": distancia de edición = 1 (insertar 'u')
- "base de datos" → "baes de datos": distancia de edición = 2 (se intercambian la 'e' y la 's')
- "Microsoft" → "Microsft": editar distancia = 1 (eliminar 'o')
Nota:
La coincidencia aproximada es computacionalmente costosa en comparación con la coincidencia exacta. Úselo estratégicamente, normalmente en conjuntos candidatos que se han filtrado previamente mediante otros criterios.
Calcular la distancia de edición con EDIT_DISTANCE
La EDIT_DISTANCE función devuelve la distancia levenshtein entre dos cadenas, que es el número mínimo de ediciones necesarias para transformar una cadena en la otra. El objetivo es buscar cadenas similares en función de un umbral definido.
En el ejemplo siguiente se muestra cómo usar 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
Puede usar EDIT_DISTANCE para buscar registros que podrían ser duplicados o coincidencias a pesar de ligeras variaciones:
-- 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);
Además, puede encontrar posibles productos duplicados:
-- 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);
Sugerencia
La distancia de edición máxima significativa depende de la longitud de la cadena. Para las cadenas cortas (5-10 caracteres), una distancia de edición de 1 a 2 indica similitud. Para cadenas más largas, podría permitir distancias de 3 a 5.
Medición de la similitud con EDIT_DISTANCE_SIMILARITY
EDIT_DISTANCE_SIMILARITY devuelve una puntuación de similitud normalizada entre 0 y 100, donde 100 representa cadenas idénticas. Esta métrica basada en porcentaje es más fácil de interpretar que la distancia de edición sin procesar, especialmente al comparar cadenas de diferentes longitudes:
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
Puede usar puntuaciones de similitud para buscar coincidencias aproximadas con un umbral como el ejemplo siguiente:
-- 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;
Cálculo de la similitud fonética con JARO_WINKLER_DISTANCE
El algoritmo Jaro-Winkler está diseñado específicamente para comparar nombres y cadenas cortas. Proporciona puntuaciones más altas a cadenas que coinciden desde el principio, lo que hace que sea especialmente eficaz para los nombres de persona en los que los prefijos son más significativos:
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
La puntuación de Jaro-Winkler oscila entre 0 y 1, donde 1 indica cadenas idénticas. Una puntuación superior a 0,9 suele indicar una coincidencia segura para los nombres.
En el ejemplo siguiente se buscan clientes con nombres similares a una entrada de búsqueda:
-- 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;
Nota:
Jaro-Winkler está optimizado para cadenas cortas como nombres. Para cadenas más largas como direcciones o descripciones, EDIT_DISTANCE_SIMILARITY a menudo proporciona mejores resultados.
Consideraciones sobre el rendimiento
Las funciones de coincidencia difusa examinan cada carácter de ambas cadenas, lo que las hace intensivas en cálculos. La comparación exacta de cadenas puede interrumpirse cuando difieren los caracteres y las búsquedas indexadas usan un recorrido eficiente de árbol B. En cambio, los algoritmos aproximados deben calcular las puntuaciones de similitud carácter por carácter. Para una tabla con un millón de filas, una búsqueda aproximada nooptimizada podría realizar un millón de cálculos de similitud, cada uno con docenas de comparaciones de caracteres.
La clave para una coincidencia difusa eficaz es reducir el conjunto de candidatos antes de aplicar las costosas funciones difusas. Use columnas indexadas con LIKE patrones, coincidencias exactas en campos relacionados o filtros de intervalo para restringir primero los resultados. Solo entonces se aplica las coincidencias parciales en el conjunto de candidatos más pequeño.
En los ejemplos siguientes se muestra este enfoque de filtrado progresivo:
-- 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;
Importante
Las funciones de coincidencia de cadenas aproximadas como EDIT_DISTANCE, EDIT_DISTANCE_SIMILARITYy JARO_WINKLER_DISTANCE están disponibles en SQL Server 2025 y versiones posteriores, Azure SQL Database y bases de datos SQL en Microsoft Fabric. Consulte la documentación de la plataforma para obtener una disponibilidad específica de características.
Para obtener más información sobre la coincidencia difusa de cadenas, vea Funciones de cadena.