searching for address matches using sql server 2008 full text search

Question

searching for address matches using sql server 2008 full text search

Sridhar-137443

Hall of Fame

Points: 3762
More actions
January 6, 2010 at 8:18 am

#208703

Hi,
I am not sure how to search for address matches using sql server 2008 full text search.
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.
Thanks, sridhar.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

I'd start by reading this to get an understanding of the options available. I think, and I'm not a full-text expert, you would want to use the FREETEXT function

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Paul White SSC Guru Points: 150467 More actions · Answer 2

You might not want to full text index each column: full text search works best when searching over the 'full text' 😉

One approach would be to full text index a column containing the entire address, and then generate a full text query from the user's input.

For example, a column containing the string "123 Apple street, Tampa FL 33647" could be searched with a query like CONTAINSTABLE(table, column, N'123 AND Apple AND Street AND FL AND 33647', 50). That would return the top 50 ranked matches with all the search terms exactly. You could add prefix searches, weighting using ISABOUT, and so on just by varying the syntax of the full text query.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Sridhar-137443 Hall of Fame Points: 3762 More actions · Answer 3

Thank you. That is what I am planning to do. I am just checking to see if there is any other way to do this with out actually creating a new column. I figured that full text search will be easy to use if you want to search for specific term against multiple columns.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 4

Paul,

But wouldn't using CONTAINSTABLE(table, *, N'123 AND Apple AND Street AND FL AND 33647', 50) do the same thing as adding a column to Full Text on with the entire address because it will search all the columns in the FT Index for those search terms?

I'm just starting to read up on Full Text search now so please correct my understanding?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Paul White SSC Guru Points: 150467 More actions · Answer 5

Jack Corbett (1/8/2010)
But wouldn't using CONTAINSTABLE(table, *, N'123 AND Apple AND Street AND FL AND 33647', 50) do the same thing as adding a column to Full Text on with the entire address because it will search all the columns in the FT Index for those search terms?

Hey Jack,

Sadly, no. The full text search is applied to each column in turn when star is specified, not over all columns at once. So, the ANDed conditions would need to match data in just one of the columns. This may be slightly counter-intuitive, but there you go.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

jcb SSCertifiable Points: 6574 More actions · Answer 6

Its difficult to find info about FTS in SSC.

I was just about to create a net topic but i think this old ill do.

I just make a example:

-- 0. create dummy table

CREATE TABLE dummy_table

(

id int identity constraint PK_id primary key

,name varchar(100)

,surname varchar(100)

)

-- 0.1 populate dummy table

insert into dummy_table values ('AAs','11s')

insert into dummy_table values ('AAs','22s')

insert into dummy_table values ('BBs','11s')

insert into dummy_table values ('BBs','22s')

insert into dummy_table values ('AAs BBs','11s 22s')

-- 1. create catalog

CREATE FULLTEXT CATALOG ctg_TESTE WITH ACCENT_SENSITIVITY = OFF

GO

-- 2. create index

CREATE FULLTEXT INDEX ON dummy_table

(

name,

surname

)

KEY INDEX PK_id

ON ctg_TESTE

WITH STOPLIST = SYSTEM, CHANGE_TRACKING OFF, NO POPULATION;

GO

-- 3. populate index

ALTER FULLTEXT INDEX ON dummy_table START FULL POPULATION;

GO

-- first issue, using * to search in both columns causes the predicate to mach for each column, no for row

select * from dbo.dummy_table

where contains(*,'AAs and 11s')

select * from dbo.dummy_table

where contains(*,'AAs') and contains(*,'11s')

---------------------------------------------

-- second issue, same thing using the wild card

select * from dbo.dummy_table

where contains(*,'"AA BB*"')

select * from dbo.dummy_table

where contains(*,'"AA 11*"')

---------------------------------------------

So, I ill really need to concatenate the varchar columns to make it owrk properly?

There are others wild cards to use with FTS?