FREETEXT or Other text search 1

[pkailas]

I'm trying to figure out how to efficiently search for a city in our database even if it is slightly misspelled. I've activated FULL Text searching and it works fine on a 2 word city name as long as one of the words is spelled correctly.

ie. Grand Rapds will bring up many cities, including the one I want "Grand Rapids"

However, if I type "Casnovi" I cannot seem to find "Casnovia"

I've seen on some web sites where even "CSNOVA" finds "Casnovia". Any thoughts on how they are doing it?

I use currently:

SELECT DISTINCT CITY, STATECODE FROM ZIPCODES WHERE FREETEXT (CITY, '"CSNOVA"')

Anyone?

Thanks,

Paul



_______
I love small animals, especially with a good brown gravy....

 

[RobertT687]

You might want to take a look at the SOUNDEX function in BOL.

SELECT DISTINCT CITY, STATECODE FROM ZIPCODES WHERE SOUNDEX(CITY) = SOUNDEX('CSNOVA').

Both 'Casnovia' and 'Csnova' return the same value (C251) from the SOUNDEX function.

See also the DIFFERENCE function to widen the closeness of the match.

 

[pkailas]

Robert,

Thanks for you assistance. Using both the FREETEXT and SOUNDEX I'm able to make a quality search for city.

First I try FREETEXT and if no records I try the SOUNDEX. I don't run both at the same time because SOUNDEX will return erroneous responses, but it does bring back alternatives if the FREETEXT fails.

Thanks again..

Paul

_______
I love small animals, especially with a good brown gravy....