I’m trying to match addresses in a table using full text indexes. I’m having trouble with the CITY column.
CITY names are often vanity names. For example, the city of Los Angeles has a city referred to as “Hollywood.” Hollywood is a vanity city and the real city is “Los Angeles.”
What I’d like is to be able to match on CITY if an address matching “Hollywood” exists but ignore CITY if there is no match so that my query will find “Los Angeles.”
Suppose there is no row with CITY=“Hollywood”, what I’m finding is that no matter what arguments I place on the full text match I will not get a match:
WHERE
zip = ‘90027’
AND MATCH( city_ft, ‘Hollywood’ ) using best_fields WITH ( fuzziness = 40 )
Does anyone know of a way to make the MATCH on city_ft “fuzzy” enough to return other results but to still have some amount of weight if there are rows where CITY=“Hollywood” is true?
Thanks, Scott