Search with 80% accuracy

Question

Search with 80% accuracy

bernypaul

SSC Enthusiast

Points: 131
More actions
June 18, 2012 at 9:18 pm

#257711

Hi all,
I am stuck up here. I am not sure how should I go for it.
I have a contact table. That have firstname, lastname, address etc. as columns. There is an additional column searchcolumn which posses all the other columns combined and separated with a white space.
Now, I need a search functionality, which should be like, it should find out similar records with at least 80% accuracy in the fields when a new item is being entered.
To make clear, say, there is already a record as John Smith New York. If a new entry comes as John Smit Newyork, it should find out the first record having the similarity.
Can anyone help me with this? Any already-known script or algorithm?
Thanks,
Berny.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Dwain Camps SSC Guru Points: 86978 More actions · Answer 1

I have no idea what I'm talking about here because I've never used it, but you might want to look at this link: http://msdn.microsoft.com/en-us/library/ms187384.aspx

You can also try it with this example:

DECLARE @t TABLE (FirstName VARCHAR(10), LastName VARCHAR(10), Addr VARCHAR(10), Combo VARCHAR(30))

INSERT INTO @t

SELECT 'John', 'Smith', 'New York', 'John Smith New York'

SELECT SOUNDEX(Combo), SOUNDEX('John Smit Newyork'), DIFFERENCE(Combo, 'John Smit Newyork')

FROM @t

SELECT SOUNDEX(Combo), SOUNDEX('Dwain C Bangkok'), DIFFERENCE(Combo, 'Dwain C Bangkok')

FROM @t

I would be interested to know if this gets you anywhere.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

vinu512 SSCoach Points: 15729 More actions · Answer 2

Hi, Welcome To SSC.

It is never supposed to be this simple but won't the following get you the 80% accurate Search you need??

Select Top 1 * From <TableName>

Where FirstName LIKE 'John%' AND LastName LIKE 'Smith%' AND Address LIKE '%NewYork%'

This is the only thing that I understood by "find out similar records with at least 80% accuracy" and "To make clear, say, there is already a record as John Smith New York. If a new entry comes as John Smit Newyork, it should find out the first record having the similarity."

I dunno if this is what you are looking for. Please elaborate on your requirement if you are looking for something else.

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Fuzzy matching generates a similarity index between two strings: try this and this.

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

bernypaul SSC Enthusiast Points: 131 More actions · Answer 4

Thanks for the reply. But this will not solve my problem. There may be small spelling differences in between names. Just like 'Aravind' and 'Arwind'. The algorithm should cite this as a match.

vinu512 (6/18/2012)
Hi, Welcome To SSC.
It is never supposed to be this simple but won't the following get you the 80% accurate Search you need??
Select Top 1 * From <TableName>
Where FirstName LIKE 'John%' AND LastName LIKE 'Smith%' AND Address LIKE '%NewYork%'
This is the only thing that I understood by "find out similar records with at least 80% accuracy" and "To make clear, say, there is already a record as John Smith New York. If a new entry comes as John Smit Newyork, it should find out the first record having the similarity."
I dunno if this is what you are looking for. Please elaborate on your requirement if you are looking for something else.

bernypaul SSC Enthusiast Points: 131 More actions · Answer 5

Thanks everyone for your replies and suggestions. I will implement it one by one and let you know the results. Thanks again!

Regards,

Berny.