CODEWITHSUNDEEP
sqlsql-servert-sql
Martin James
Martin James

Reputation: 146

Full text search VS Fuzzy Search based on many columns

I have an Employee table with these columns :

I have a form with a search text input, where a user can type one column or all of them there like for example :

What is the difference between Full text search and Fuzzy Search in SQL Server in this case?

Upvotes: 0

Views: 1267

Answers (4)

user19318166
user19318166

Reputation: 1

A very good solution is searloc. It is a CLR library with zero dependencies and with many features.

It supports full text search, phonetic match for all languages, keyboard match, fuzzy search, multi columns search, and many others.

And the most important it is very fast, it needs just a few milliseconds for millions of records.

Upvotes: 0

Doug Coats
Doug Coats

Reputation: 7117

Well we know that the main difference between fuzzy and full text is "exactly what im looking for" versus "similarities." As that applies to SQL Server and to your example given, is fulltext search on firstname as opposed to mining similarities across three different columns, which might give a lot of noise depending on how clean your data is or how "guided" the end user experience in terms of entering those values is.

With fulltext search on fullname, im pretty sure that its a straight forward answer:

SELECT * FROM tblEmployee WHERE FullName=@FullNamParameter;

With fuzzy search, it can get very gross and nasty, as without clearer understanding of what the UI is doing I am forced to assume we want to check similarities on all parameters for all fields. TO be fairly honest Im pretty sure this query is the absolutel worst but it does demonstrate the idea for you.

SELECT * FROM tblEmployee WHERE FullName LIKE '%'+@Parameter1+'%'
UNION
SELECT * FROM tblEmployee WHERE FullName LIKE '%'+@Parameter2+'%'
UNION
SELECT * FROM tblEmployee WHERE FullName LIKE '%'+@Parameter3+'%'
UNION
SELECT * FROM tblEmployee WHERE Phone LIKE '%'+@Parameter1+'%'
UNION
SELECT * FROM tblEmployee WHERE Phone LIKE '%'+@Parameter2+'%'
UNION
SELECT * FROM tblEmployee WHERE Phone LIKE '%'+@Parameter3+'%'
UNION
SELECT * FROM tblEmployee WHERE Team LIKE '%'+@Parameter1+'%'
UNION
SELECT * FROM tblEmployee WHERE Team LIKE '%'+@Parameter2+'%'
UNION
SELECT * FROM tblEmployee WHERE Team LIKE '%'+@Parameter3+'%'

Upvotes: 1

eshirvana
eshirvana

Reputation: 24633

so you have to options :

  1. using full text search :

if the data is huge and you are looking for scalable data search , this method is preferable , however harder to maintain . so I suggest you add a computed column in that table and put and full text index on that:

alter table tablename
add column cmptcolumn as concat_ws(',',EmployeeId,FullName,PhoneNumber,...)

--full text catalog 
CREATE FULLTEXT CATALOG catalogName AS DEFAULT;  

-- full text index
create full text index on tablename (cmptcolumn)

-- search :

select * from tablename
where contain(cmptcolumn, 'SearchString');

by full text search you can search for synonyms and also words related to each other as well:

select * from tablename
where freetext(cmptcolumn, 'SearchString');

read more about different full text search options here

  1. using search query. witch again you can benefit from computed column or search inside each column separately:
select *
from tablename
where (Fullname like '%'+@fullNameSearchString+'%' or @fullNameSearchString is null)
and (Department = @DepartmentSearchString or @DepartmentSearchString is null)
and ...

while first method is a faster way to search insode strings, second method provides more accurate result . however 'FreeText' looks for meaning of the word as well, in that case it might be slower.

in the second method either way you go ( with or without computed column) , having index on the column(s) in a necessity to improve the performance , however using like '%%' usually can't use index as it should.

Upvotes: 1

Jayrag Pareek
Jayrag Pareek

Reputation: 374

create stored procedure and pass column comma separated check this example:

  CREATE Procedure [dbo].Create_Sp
    @SearchColumn  varchar(500)=null
  AS 
  BEGIN 
    
    DECLARE @SQL nvarchar(max)    
    
    SELECT @SQL = N'select '+@SearchColumn+' from #Employee'
    
    EXECUTE sp_executesql @SQL
  End

Upvotes: 1

Related Questions