searching for address matches using sql server 2008 full text search

Question

Hi,

I am not sure how to search for address matches using sql server 2008 full text search. This is what I tried but it doesn't return any records.

TableA
------
Address1
Address2
City
State
Zip

All the above columns in the table are full text indexed. Let's say if the user enters "123 Apple street FL 33647" and I have a record in the table as

Address1 = "123" , Address2 = "Apple street", City = "Tampa", State = "FL" and Zip = "33647" I would like the query to return this. can you please let me know how I would do this.

query tried
--------------
SELECT * FROM TableA 
WHERE CONTAINS((Address1, Address2, City, State, zip), 
N'FORMSOF(THESAURUS, 123AppleStreetFL33647)');

If I put spaces in the search word, it is giving syntax error.

Thanks, sridhar.

Answer 1

You have to use the correct syntax:

SELECT * FROM TableA 
WHERE CONTAINS((Address1, Address2, City, State, zip), 
N'FORMSOF(THESAURUS, 123) AND FORMSOF(THESAURUS,'Apple') .. etc);

See documentation