Full Text Search Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Full Text Search Help

Author

Topic

sqldbaa
Starting Member

32 Posts

Posted - 2009-09-02 : 10:06:38

Hi ,

Can anyone let me know how to do a Address search in Fulltext search.

i have created fulltext catalog, index on a table

Table has
Add_Num INT
Address VARCHAR

Add_Num Address
132 1/113 b/1 Rajiv Nagar North
78 525 Washington Rd
342 Serova Way 14
456 10 Washington Blvd

Address field is fulltext indexed.

Only using FREETEXT, im able to get the results, but i gives all probale results.I am not able to do with contains option

SELECT * FROM Addresses WHERE CONTAINS(Address,'"Washington Rd"')
Should give me

Add_Num Address
78 525 Washington Rd
456 10 Washington Blvd

Please help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-02 : 10:09:52

how would second result be returned as it doesnt contain Washington Blvd?

sqldbaa
Starting Member

32 Posts

Posted - 2009-09-02 : 10:24:49

Since the Address column is full text indexed , on full text searching
with the condition

WHERE CONTAINS(Address,'"Washington Rd"')
the result should inculde all rows which has "Washington" or "Rd".
That is why full text search option is used(to give probable matched)

If suppose the table has

Add_Num Address
132 1/113 b/1 Rajiv Nagar North
78 525 Washington Rd
342 Serova Way 14
456 10 Washington Blvd
555 Washington St
456 Serova Rd

it should give result as

Add_Num Address
78 525 Washington Rd
456 10 Washington Blvd
555 Washington St
456 Serova Rd

Since the Address field has Alpha numeric characters the query is failing.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-02 : 10:27:16

what about this?

WHERE CONTAINS(Address,'"Washington"')

sqldbaa
Starting Member

32 Posts

Posted - 2009-09-02 : 10:43:04

WHERE CONTAINS(Address,'"Washington"')
this works

But '"Washington Rd"' comes as a parameter to the query. There many cases like this.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-02 : 10:50:22

i cant understand how you expect it to return all strings containing washingdon alone to be returned when you're passing Washington Rd as value.