SQL Close Match

POB · 8 Nov 2009 at 10:25

I'm trying to find a function that can return a close match in SQL.

For example if a user enters Llondon the search should return London as an option.

Closest type of concept would be a spell checker.

I have experimented with the Soundex function, but its not sutible for correcting input errors in this way.

Any ideas?

Stelly · 8 Nov 2009 at 10:32

I dont think you could to be honest, thats why a lot of people use dropdownlists to choose cities, or give the user a list of choices from the DB, its doable but it would be a massive massive complex SQL query to do something like that.

Stelly

POB · 8 Nov 2009 at 10:45

Thanks for the input.

I'm currently downloading the full text search add in for MSSQL to see if that has anything usefull in it.

Stelly · 8 Nov 2009 at 11:16

aaaaaah cool... let me know if you need any help I actually have one of them I wrote last year for MSSQL...

Stelly

djkav · 8 Nov 2009 at 17:20

POB said:
I'm trying to find a function that can return a close match in SQL.

For example if a user enters Llondon the search should return London as an option.

Closest type of concept would be a spell checker.

I have experimented with the Soundex function, but its not sutible for correcting input errors in this way.

Any ideas?

Here is a web article all about fuzzy logic searching beyond soundex. Have a read

http://anastasiosyal.com/archive/2009/01/11/18.aspx

Hades · 8 Nov 2009 at 19:54

You could try the following:

* First search for an exct match. If that's not found then go to the next step.
* Break the word up into sections (e.g. groups of three letters).
* Perform a search using LIKE on each of the sections.
* Using the results above, let the user choose the exact match.

However, the above is not very efficient. But it gets the job done.