Нечёткое сопоставление строк в SQL Server 2025

Автор: Leonard Lobel, Fuzzy String Matching in SQL Server 2025

Нечёткое сопоставление строк (Fuzzy String Matching) — это процесс поиска строк, которые приблизительно равны, а не точно совпадают. Это критически важная возможность для очистки данных, дедупликации, простого поиска по естественному языку и сопоставления пользовательского ввода с известными значениями.

До сих пор возможности нечёткого сопоставления в SQL Server были ограничены фонетическими сравнениями. Но теперь SQL Server 2025 представляют набор современных функций сходства строк, которые можно запускать напрямую в T-SQL с гораздо большей точностью.

Примечание: Эта статья основана на SQL Server 2025 CTP 2.1. Синтаксис и поведение могут претерпеть незначительные изменения к моменту выпуска продукта. Нечёткое сопоставление строк в конечном итоге будет поддерживаться во всех редакциях SQL Server, включая SQL Server 2025 для Windows, SQL Server 2025 для Linux, Azure SQL Database и Managed Instance.

Наследие: SOUNDEX и DIFFERENCE

Эти две функции существуют уже десятилетиями:

• SOUNDEX: Создаёт код, представляющий фонетическое звучание слова (например, «Green» → G650, «Greener» → G656).
• DIFFERENCE: Сравнивает коды SOUNDEX по шкале от 1 до 4 (4 ≈ точное совпадение).

Хотя они работают удовлетворительно для быстрых фонетических проверок, они не подходят для длинных строк или тонких сравнений.

Новое в SQL Server 2025: четыре современных функции нечёткого сопоставления

Теперь в SQL Server 2025 встроены эти хорошо известные алгоритмы нечёткого сопоставления:

Имя функции	Возвращает	Сценарии использования
EDIT_DISTANCE	Количество изменений (вставка/удаление/замена) между двумя строками (расстояние Левенштейна)	Нужен простой счётчик «сколько изменений?» или сортировка по степени изменений
EDIT_DISTANCE_SIMILARITY	Нормализованный процент сходства (0–100) (сходство Левенштейна)	Нужна ранжируемая оценка или пороги, например >= 80
JARO_WINKLER_DISTANCE	Оценка расстояния, оптимизированная для коротких строк (меньше — лучше)	Предпочитается обратная оценка (расстояние)
JARO_WINKLER_SIMILARITY	Оценка сходства с упором на общие префиксы (больше — лучше)	Имена и короткие значения, подверженные опечаткам

Исследование на парах словах

Создадим таблицу пар слов и оценим их с помощью как новых, так и старых функций.

CREATE TABLE WordPair (
  WordPairId    int IDENTITY PRIMARY KEY,
  Word1         varchar(50),
  Word2         varchar(50)
)

Теперь заполним таблицу некоторыми тестовыми данными:

INSERT INTO WordPair VALUES
 ('Colour',     'Color'),
 ('Flavour',    'Flavor'),
 ('Centre',     'Center'),
 ('Theatre',    'Theater'),
 ('Theatre',    'Theatrics'),
 ('Theatre',    'Theatrical'),
 ('Organise',   'Organize'),
 ('Analyse',    'Analyze'),
 ('Catalogue',  'Catalog'),
 ('Programme',  'Program'),
 ('Metre',      'Meter'),
 ('Honour',     'Honor'),
 ('Neighbour',  'Neighbor'),
 ('Travelling', 'Traveling'),
 ('Grey',       'Gray'),
 ('Green',      'Greene'),
 ('Green',      'Greener'),
 ('Green',      'Greenery'),
 ('Green',      'Greenest'),
 ('Orange',     'Purple'),      -- совершенно разные
 ('Defence',    'Defense'),
 ('Practise',   'Practice'),
 ('Practice',   'Practice'),    -- идентичные
 ('Aluminium',  'Aluminum'),
 ('Cheque',     'Check')

Эта выборка включает смесь близких совпадений (британское и американское написание), небольших вариаций («Green» и «Greener»), одну намеренно точную пару («Practice» и «Practice») и одну намеренно плохую пару («Orange» против «Purple»).

Теперь оценим каждую пару с помощью новых и старых функций:

SELECT
  *,
  -- Новые функции нечёткого сопоставления SQL Server 2025
  LevenshteinDistance     = EDIT_DISTANCE(Word1, Word2),
  LevenshteinSimilarity   = EDIT_DISTANCE_SIMILARITY(Word1, Word2),
  JaroWrinklerDistance    = JARO_WINKLER_DISTANCE(Word1, Word2),
  JaroWrinklerSimilarity  = JARO_WINKLER_SIMILARITY(Word1, Word2),
  -- Старые функции нечёткого сопоставления SQL Server
  Soundex1                = SOUNDEX(Word1),
  Soundex2                = SOUNDEX(Word2),
  Difference              = DIFFERENCE(Word1, Word2)
FROM
  WordPair
ORDER BY
  LevenshteinSimilarity DESC

Этот запрос задействует четыре новые функции вместе с двумя старыми для каждой строки. Результаты сортируются по убыванию EDIT_DISTANCE_SIMILARITY (0–100; 100 = точное совпадение), что даёт быстрое и простое сравнение.

Запустите запрос и понаблюдайте:

• Точное совпадение «Practice/Practice» оказывается на самом верху (сходство 100, расстояние 0).
• Близкие пары, такие как «Grey/Gray» и «Colour/Color», группируются вверху с высоким сходством и небольшим расстоянием редактирования.
• Выброс («Orange/Purple») опускается вниз с низким сходством и большим расстоянием.

Вы увидите, что старые SOUNDEX/DIFFERENCE иногда переоценивают или недооценивают определённые пары, в то время как более новые метрики улавливают нюансы (вставки, замены, транспозиции, вес префикса).

-- Очистка тестовой таблицы
DROP TABLE WordPair

Дедупликация в реальном мире (записи клиентов)

Теперь давайте обнаружим потенциальные дубликаты в таблице клиентов, оценив сходство имён, фамилий и адресов, а затем объединив их.

CREATE TABLE Customer (
  CustomerId int IDENTITY PRIMARY KEY,
  FirstName varchar(50),
  LastName varchar(50),
  Address varchar(100)
)

Эта минималистичная структура позволит сосредоточиться на нечётком сопоставлении. Теперь заполним таблицу тестовыми данными, включая смесь точных дубликатов и почти дубликатов по полям имени, фамилии и адреса.

INSERT INTO Customer VALUES
 ('Johnathan',  'Smith',    '123 North Main Street'),
 ('Jonathan',   'Smith',    '123 N Main St.'),
 ('Johnathan',  'Smith',    '456 Ocean View Blvd'),
 ('Johnathan',  'Smith',    '123 North Main Street'),
 ('Daniel',     'Smith',    '123 N. Main St.'),
 ('Danny',      'Smith',    '123 N. Main St.'),
 ('John',       'Smith',    '123 Main Street'),
 ('Jonathon',   'Smyth',    '123 N Main St'),
 ('Jon',        'Smith',    '123 N Main St.'),
 ('Johnny',     'Smith',    '124 N Main St'),
 ('Ethan',      'Goldberg', '742 Evergreen Terrace'),
 ('Carlos',     'Rivera',   '456 Ocean View Boulevard'),
 ('Carlos',     'Rivera',   '456 Ocean View Blvd'),
 ('Carl',       'Rivera',   '456 Ocean View Boulevard'),
 ('Carlos',     'Rivera',   '456 Ocean View Boulevard')

SELECT * FROM Customer

Оценка сходства имён

Мы вычислим попарное сходство имён для всех комбинаций строк, используя JARO_WINKLER_SIMILARITY (отлично подходит для коротких строк и чувствительности к префиксу).

;WITH PairwiseSimilarityCte AS (
  SELECT
    CustomerId1         = c1.CustomerId,
    CustomerId2         = c2.CustomerId,
    FirstName1          = c1.FirstName,
    FirstName2          = c2.FirstName,
    FirstNameSimilarity = JARO_WINKLER_SIMILARITY(c1.FirstName, c2.FirstName)
  FROM
    Customer AS c1
    INNER JOIN Customer AS c2 ON c2.CustomerId < c1.CustomerId
)
SELECT
  *,
  FirstNameQuality = CASE
    WHEN FirstNameSimilarity = 1    THEN 'Точное'
    WHEN FirstNameSimilarity >= .85 THEN 'Очень сильное'
    WHEN FirstNameSimilarity >= .75 THEN 'Сильное'
    WHEN FirstNameSimilarity >= .4  THEN 'Слабое'
                                    ELSE 'Очень слабое'
    END
FROM
  PairwiseSimilarityCte
ORDER BY
  FirstNameSimilarity DESC

Как это работает:

• Самосоединение c2.CustomerId < c1.CustomerId генерирует уникальные неупорядоченные пары (без дубликатов, без самосочетаний).
• CTE вычисляет сходство для каждой пары; внешний запрос помечает его понятными категориями.

Что ожидать:

• Точные повторы (например, идентичные имена) показывают FirstNameSimilarity = 1 и метку «Точное».
• Варианты, такие как Johnathan/ Jonathan и Jon/ John, группируются как «Очень сильное» или «Сильное».
• Не связанные имена (например, Daniel / Johnathan) попадают в «Слабое»/«Очень слабое».

Оценка сходства фамилий

Тот же шаблон, теперь сфокусированный на фамилиях. Это покажет, что фонетически похожие варианты, такие как Smith/Smyth, получают высокие оценки.

;WITH PairwiseSimilarityCte AS (
  SELECT
    CustomerId1         = c1.CustomerId,
    CustomerId2         = c2.CustomerId,
    LastName1           = c1.LastName,
    LastName2           = c2.LastName,
    LastNameSimilarity  = JARO_WINKLER_SIMILARITY(c1.LastName, c2.LastName)
  FROM
    Customer AS c1
    INNER JOIN Customer AS c2 ON c2.CustomerId < c1.CustomerId
)
SELECT
  *,
  LastNameQuality = CASE
    WHEN LastNameSimilarity = 1     THEN 'Точное'
    WHEN LastNameSimilarity >= .85  THEN 'Очень сильное'
    WHEN LastNameSimilarity >= .75  THEN 'Сильное'
    WHEN LastNameSimilarity >= .4   THEN 'Слабое'
                                    ELSE 'Очень слабое'
    END
FROM
    PairwiseSimilarityCte
ORDER BY
    LastNameSimilarity DESC

Что ожидать:

• Все пары Smith/Smith оцениваются как «Точное».
• Smith/Smyth должны получить оценку «Очень сильное» (небольшое изменение в написании).
• Совершенно разные фамилии (например, Goldberg / Smith) получают низкие оценки.

Оценка сходства адресов

Адреса часто «шумные» (сокращения, пунктуация, порядок). JARO_WINKLER_SIMILARITY хорошо справляется с короткими транспозициями и совпадениями префиксов.

;WITH PairwiseSimilarityCte AS (
  SELECT
    CustomerId1         = c1.CustomerId,
    CustomerId2         = c2.CustomerId,
    Address1            = c1.Address,
    Address2            = c2.Address,
    AddressSimilarity   = JARO_WINKLER_SIMILARITY(c1.Address, c2.Address)
  FROM
    Customer AS c1
    INNER JOIN Customer AS c2 ON c2.CustomerId < c1.CustomerId
)
SELECT
  *,
  AddressQuality = CASE
    WHEN AddressSimilarity = 1      THEN 'Точное'
    WHEN AddressSimilarity >= .85   THEN 'Очень сильное'
    WHEN AddressSimilarity >= .75   THEN 'Сильное'
    WHEN AddressSimilarity >= .4    THEN 'Слабое'
                                    ELSE 'Очень слабое'
  END
FROM
  PairwiseSimilarityCte
ORDER BY
  AddressSimilarity DESC

Что ожидать:

• Точные дубликаты возглавляют список.
• Варианты, такие как «123 North Main Street» против «123 N Main St.», получают оценку «Очень сильное»/«Сильное».
• Не связанные адреса (например, «742 Evergreen Terrace» против «123 N. Main St.») получают низкие оценки.

Объединение всех трёх: составная оценка совпадения

Высокая оценка имени плюс низкая оценка адреса, вероятно, не являются истинным дубликатом. Давайте усредним три оценки сходства в FinalCombinedScore для лучшего общего ранжирования.

;WITH PairwiseSimilarityCte AS (
  SELECT
    CustomerId1         = c1.CustomerId,
    CustomerId2         = c2.CustomerId,
    FirstName1          = c1.FirstName,
    FirstName2          = c2.FirstName,
    FirstNameSimilarity = JARO_WINKLER_SIMILARITY(c1.FirstName, c2.FirstName),
    LastName1           = c1.LastName,
    LastName2           = c2.LastName,
    LastNameSimilarity  = JARO_WINKLER_SIMILARITY(c1.LastName, c2.LastName),
    Address1            = c1.Address,
    Address2            = c2.Address,
    AddressSimilarity   = JARO_WINKLER_SIMILARITY(c1.Address, c2.Address)
  FROM
    Customer AS c1
    INNER JOIN Customer AS c2 ON c2.CustomerId < c1.CustomerId
),
FinalCombinedScoreCte AS (
  SELECT
    *,
    FinalCombinedScore = (FirstNameSimilarity + LastNameSimilarity + AddressSimilarity) / 3.0
  FROM
    PairwiseSimilarityCte
)
SELECT
  *,
  FinalQuality = CASE
    WHEN FinalCombinedScore = 1     THEN 'Точное'
    WHEN FinalCombinedScore >= .85  THEN 'Очень сильное'
    WHEN FinalCombinedScore >= .75  THEN 'Сильное'
    WHEN FinalCombinedScore >= .4   THEN 'Слабое'
                                    ELSE 'Очень слабое'
  END
FROM
  FinalCombinedScoreCte
ORDER BY
  FinalCombinedScore DESC

Как это работает:

• Первое CTE вычисляет три оценки сходства для каждой пары.
• Второе CTE усредняет их в FinalCombinedScore.
• Внешний запрос помечает и сортирует по этой объединённой метрике.

Что ожидать:

• Истинные дубликаты (идентичные имя, фамилия, адрес) появляются прямо наверху («Точное»).
• Пары, такие как «Jonathon Smyth, 123 N Main St» и «Johnathan Smith, 123 North Main Street», занимают высокие позиции, потому что имена и адреса очень похожи.
• Пары, которые имеют общее имя, но различаются адресом (например, Johnathan Smith по адресу 456 Ocean View Blvd против 123 North Main), занимают более низкие позиции — именно то, что нужно при дедупликации.

Заключительные мысли

Новые функции нечёткого сопоставления строк в SQL Server 2025 наконец-то предоставляют T-SQL полноценную оценку сходства. Независимо от того, очищаете ли вы устаревшие данные, объединяете списки клиентов или создаёте функции, устойчивые к опечаткам, теперь вы можете делать это нативно, эффективно и с той степенью детализации, которую вы всегда хотели.