CODEWITHSUNDEEP
sql-servert-sqlssisfuzzy-search
ScottStonehouse
ScottStonehouse

Reputation: 24965

Is it possible to perform T-SQL fuzzy lookup without SSIS?

SSIS 2005/2008 does fuzzy lookups and groupings. Is there a feature that does the same in T-SQL?

Upvotes: 9

Views: 22076

Answers (4)

Ira Warren Whiteside
Ira Warren Whiteside

Reputation:

On March 5 2009 I will have an article posted on www.sqlservercentral.com with a sample of Jaro-Winkler TSQL

Upvotes: 1

vfilby
vfilby

Reputation: 10008

Fuzzy lookup uses a q-gram approach, by breaking strings up into tiny sub-strings and indexing them. You can then then search input by breaking it up into equally sized strings. You can inspect the format of their index and write a CLR function to use the same style of index but you might be talking about a fair chunk of work.

It is actually quite interesting how they did it, very simple yet provides very robust matching and is very configurable.

From that I recall of the index when I last looked at it, each q-gram or substring is stored in a row in an table (the index). That row contains an nvarchar column (among other values) that is used as binary data and contains references to the rows that match.

There is also an open feedback suggestion on Microsoft Connect for this feature.

Upvotes: 7

Josef
Josef

Reputation: 7609

Full Text Search is a great fuzzy tool. Brief primer here

Upvotes: 3

Ian Boyd
Ian Boyd

Reputation: 256931

SQL Server has a SOUNDEX() function:

SELECT * 
FROM Customers
WHERE SOUNDEX(Lastname) = SOUNDEX('Stonehouse')
AND SOUNDEX(Firstname) = SOUNDEX('Scott')

Upvotes: 7

Related Questions