SQL Server Full text search to find an address

Phil Catterall
Phil Catterall used Ask the Experts™
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 ?
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2012

I have some triggers which populate these with the desired search fields, comma separated.
So you are duplicating the data by copying the values from some columns to build a searchable column?  If so why?  Why not add all the columns to the full text search?
Solution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)
If you have full Text search enabled then use Contains Function to search your term in column.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial