Link to home
Start Free TrialLog in
Avatar of Phil Catterall
Phil Catterall

asked on

SQL Server Full text search to find an address

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 ?
Thanks
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

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?
ASKER CERTIFIED SOLUTION
Avatar of Alpesh Patel
Alpesh Patel
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial