Implementing Simple Fuzzy Search in SQL Queries with MSSQL

LAI TOCA
2 min readDec 13, 2024

--

Create by Bing

In some scenarios, we might need to perform fuzzy searches across multiple columns in a SQL table. This article introduces an easy way to achieve this functionality using Microsoft SQL Server (MSSQL).

Example Use Case

Suppose we have a mapping table containing Customer_Code and Customer_Name, as shown below:

Solution Outline

The following steps demonstrate how to implement fuzzy search:

  1. Create a Temporary Table: Concatenate the desired columns into a single column named Search.
  2. Use a Case Expression and LIKE Operator: Apply the fuzzy search logic using SQL conditions.
create procedure [dbo].[Usp_Read_Customer_Map]
@Customer_Code int = null
, @Search varchar(50) = null
as
begin
set nocount on;
begin try
select [IDX]
, [Customer_Code]
, [Customer_Name]
, [CreateUser]
, [CreateDate]
, [UpdateUser]
, [UpdateDate]
, concat([Customer_Code], [Customer_Name], [UpdateUser]) as [Search]
into [#tempResult]
from [dbo].[Customer_Code_Name_Map];


select [IDX]
, [Customer_Code]
, [Customer_Name]
, [CreateUser]
, [CreateDate]
, [UpdateUser]
, [UpdateDate]
from [#tempResult]
where [Customer_Code] = case
when isnull(Customer_Code, 0) <= 0 then
[Customer_Code]
else
@Customer_Code
end and [Search] like case
when isnull(@Search, '') = '' then
[Search]
else
'%' + @Search + '%'
end;

drop table [#tempResult];

end try
begin catch

select error_number() as [ErrorNumber]
, error_severity() as [ErrorSeverity]
, error_state() as [ErrorState]
, error_procedure() as [ErrorProcedure]
, error_line() as [ErrorLine]
, error_message() as [ErrorMessage];
throw;
end catch;

return 0;
end;

Advantages of This Approach

  • Flexibility: Allows searching across multiple columns without complex query modifications.
  • Simplicity: Easy to implement and maintain.
  • Performance: Suitable for small to medium-sized datasets; for large datasets, consider indexing or using full-text search for optimization

Reference

--

--

LAI TOCA
LAI TOCA

Written by LAI TOCA

Coding for fun. (Either you are running for food or running for being food.)

No responses yet