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 ?
.NET ProgrammingMicrosoft SQL ServerMicrosoft SQL Server 2008

Avatar of undefined
Last Comment
Alpesh Patel

8/22/2022 - Mon
Anthony Perkins

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?
Alpesh Patel

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy