Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

searching for address matches using sql server 2008 full text search Expand / Collapse
Author
Message
Posted Wednesday, January 6, 2010 8:18 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, June 5, 2014 7:52 AM
Points: 454, Visits: 194
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.
Post #842848
Posted Thursday, January 7, 2010 6:54 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:23 PM
Points: 10,214, Visits: 13,161
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #843492
Posted Thursday, January 7, 2010 8:14 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 10:07 PM
Points: 9,926, Visits: 11,183
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #844065
Posted Friday, January 8, 2010 6:28 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, June 5, 2014 7:52 AM
Points: 454, Visits: 194
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.
Post #844237
Posted Friday, January 8, 2010 8:28 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:23 PM
Points: 10,214, Visits: 13,161
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #844379
Posted Friday, January 8, 2010 11:57 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 10:07 PM
Points: 9,926, Visits: 11,183
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #844825
Posted Thursday, November 22, 2012 11:26 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 10:46 AM
Points: 2,734, Visits: 943
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?
Post #1387959
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse