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:
- Create a Temporary Table: Concatenate the desired columns into a single column named
Search. - 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
