I have a requirement to do "fuzzy logic" searching across a range of data to find an address quickly from 20,000+ Addresses.
I want to search across address line 1, line 2, town, postcode and a building reference code.
So I have created a field in my address table (called FullTextSearch Nvarchar(MAX) )and applied a FTE to it.
I have some triggers which populate these with the desired search fields, comma separated.E.g.
50888200,Canons House,Canons Way,Bristol,BS1 5LL
Now I want to enter for example "canons house Bristol" as a search term and return this record as the closest match.
Or I might enter "50888200" (a property code) or a part of it - like the first 6 characters and return a range of results
or a postcode "BS1 5LL"
and other combinations.
Essentially a very user friendly way of finding a location, based upon a number of different ways in which the property/address may be known.
We plan to add some other data in there where a building is known by a colloquial name, for example "Head Office" or "Bristol branch" or "Training centre".
Is my approach right ?
Any ideas or suggestions ?