Поиск товара по частичному вводу артикула (SKU)

Дата подготовки материала: 17:37:07 ().

В современных торговых системах часто возникает задача поиска товара по частичному или неполному вводу артикула (SKU). Пользователи могут опускать дефисы, пробелы или другие знаки препинания, тогда как в базе данных артикул хранится в точном виде, например F-120-SB. Ниже рассмотрены проверенные подходы, позволяющие выполнять поиск «как будто» специальные символы отсутствуют, а также обсуждаются их плюсы и минусы.

Постановка задачи

Имеем таблицу Products со столбцом SKU типа varchar(100). Необходимо, чтобы запрос находил запись по следующим вариантам ввода пользователя:

• Точное совпадение: F-120-SB
• Пробелы вместо дефисов: F 120 SB
• Полное удаление всех не‑алфавитно‑цифровых символов: F120SB
• Частичное совпадение без символов: F120 (префикс)

Классическое сравнение WHERE SKU = @searchTerm не покрывает последние три случая.

Варианты решения

Дополнительное вычисляемое поле (комputed column)

Создаём столбец, в котором хранится «очищенный» SKU – строка без всех букв и цифр.

ALTER TABLE Products
ADD SKU_Clean AS 
 dbo.RemoveNonAlphaNum(SKU) -- пользовательская функция, см. ниже
PERSISTED;

Теперь поиск сводится к простому сравнению:

WHERE SKU_Clean LIKE @cleanSearchTerm + '%'

Где @cleanSearchTerm – введённая пользователем строка, из которой также удалены все не‑алфавитно‑цифровые символы.

Пользовательская скалярная функция RemoveNonAlphaNum

Функция удаляет из строки всё, что не является буквой латинского алфавита или цифрой.

CREATE FUNCTION dbo.RemoveNonAlphaNum (@input varchar(100))
RETURNS varchar(100)
AS
BEGIN
 DECLARE @pos int = PATINDEX('%[^A-Z0-9]%', @input COLLATE Latin1_General_BIN);
 WHILE @pos > 0
 BEGIN
 SET @input = STUFF(@input, @pos, 1, '');
 SET @pos = PATINDEX('%[^A-Z0-9]%', @input COLLATE Latin1_General_BIN);
 END
 RETURN @input;
END;
GO

Пример использования:

DECLARE @search varchar(20) = 'F 120';
SELECT *
FROM Products
WHERE dbo.RemoveNonAlphaNum(SKU) LIKE dbo.RemoveNonAlphaNum(@search) + '%';

Таблица вариантов SKU (SKU variations)

При вводе нового товара генерируем все возможные варианты артикула (с удалением дефисов, пробелов, их комбинациями) и сохраняем их в отдельной таблице SKU_Variations:

Поиск тогда выполняется простым JOIN:

SELECT p.*
FROM Products p
JOIN SKU_Variations v ON p.ProductID = v.ProductID
WHERE v.Variation = @searchTerm;

Преимущество – максимальная скорость поиска (индекс по Variation). Недостаток – необходимость поддерживать актуальность таблицы при изменении SKU.

Полнотекстовый поиск (Full‑Text Search)

Если версия SQL Server поддерживает полнотекстовый индекс, можно создать его на столбце SKU с пользовательским стоп‑словом, включающим дефис, пробел и другие знаки.

CREATE FULLTEXT CATALOG ftCatalog AS DEFAULT;
CREATE FULLTEXT INDEX ON Products(SKU)
 KEY INDEX PK_Products
 WITH STOPLIST = SYSTEM;

Запрос:

SELECT *
FROM Products
WHERE CONTAINS(SKU, '"F120*"'); -- звёздочка означает префиксный поиск

Полнотекстовый поиск хорошо справляется с вариациями типа F 120 SB и F120SB, но требует дополнительной настройки и может быть менее точным для очень коротких префиксов.

Сравнительная таблица подходов

Рекомендации

1. Если объём товаров умеренный (до нескольких сотен тысяч строк) и допустимо небольшое дополнительное хранилище – выбираем вычисляемое поле с функцией RemoveNonAlphaNum. Это просто реализовать, поддерживать и обеспечивает хорошую скорость при наличии индекса.
2. Для очень высоких нагрузок (миллионы строк, частые поиски) целесообразно создать таблицу вариантов SKU и поддерживать её через триггер AFTER INSERT/UPDATE.
3. Если уже используется полнотекстовый поиск для других полей и требуется гибкость в поиске фраз, можно добавить SKU в тот же индекс, но следует протестировать качество совпадений для коротких префиксов.
4. Не забывайте обновлять статистику и перестраивать индексы после массовых изменений данных, особенно при использовании вычисляемых полей.

Поиск SKU без учёта специальных символов – типичная задача в e‑commerce и складских системах. Выбор оптимального решения зависит от объёма данных, частоты обновлений и требований к скорости отклика. Наиболее универсальным и простым в поддержке является подход с вычисляемым полем и пользовательской функцией удаления не‑алфавитно‑цифровых символов. При необходимости максимальной производительности можно дополнительно материализовать варианты артикулов в отдельной таблице.

Надеюсь, представленные методы помогут вам построить надёжный и быстрый поиск по артикулам, улучшив опыт пользователей и снизив количество неудачных запросов.