Full Text Search + Keywords (ANR/OR/ETC)

[RhythmAddict112]

Hi all,
Quick question. I'm doing some playing around w/FTS and it's functionality. I've been messing, specifically, with FREETEXTTABLE & CONTAINSTABLE and I have a question regarding them. Is it possible for me to use AND in the search to combine criteria? I've seen tons of examples using OR...but none w/AND. How would I go about this?

The following table is indexed for FTS...
CREATE TABLE [fts_test] (
[FirstName] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastName] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MiddleName] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HomeAddressCity] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[JobTitle] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fts_id] [int] IDENTITY (1, 1) NOT NULL ,
CONSTRAINT [PK_fts_test] PRIMARY KEY CLUSTERED
(
[fts_id]
) ON [PRIMARY]
) ON [PRIMARY]
GO

All columns (except ID) are indexed via FTS. How would I find someone who has last name "Smith" AND job title of "programmer" ? I've tried numerous permutations of double quotes and parenthesis - none with the desired results (ONLY rows with "Smith" and "Programmer")

Any thoughts? Am I out of my mind? Thanks!

http://geekswithblogs.net/sanjayu/

 

[mrdenny]

If you do CONTAINSTABLE(IndexName, *, '"Smith" and "Programmer"') it should give you what you are looking for.

You can also try doing two CONTAINS (one for each column) and joining them together based on PK.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[RhythmAddict112]

Hmmm...I actually did try the above query.

Here it is, just in case I'm missing something:

SELECT 
	JobTitle,
	LastName,
	FirstName,
	MiddleName,
	HomeAddressCity, 
	KEY_TBL.RANK
FROM fts_test 
INNER JOIN CONTAINSTABLE(dbo.fts_test, *, 
      '"bob" and "smith"'
   ) as key_tbl
ON fts_test.fts_id = KEY_TBL.[KEY] 
ORDER BY KEY_TBL.RANK DESC

I've anonyomized the name in my example, however, there is definitely a Bob Smith in the table....Unfortunately, I don't get any results. I've added all the columns in the table to the FTS index...so I don't believe it's anything I missed on that end....any ideas?

The eventual goal (that I'm determining the feasibility of) is to have a keyword search. So I could type in something like say...
"Bob and Smith and Harvard and developer III"

Unfortunately, this implies I do not know what column each of the keywords above would map to...so I don't believe I could use the second technique

Appreciate all your help!

http://geekswithblogs.net/sanjayu/

 

[mrdenny]

Humm...

That code seams to be working on my table. But all the search data for my table is within a single column.

I'm using (My system is a help desk system)

select * 
from tickets
inner join containstable (tickets, *, 'test and ticket') as key_tbl on tickets.ticket_id = key_tbl.[key]
order by Key_tbl.rank

If you can get the script to create your full text index and can post that I'll create it on one of my systems and see if I can't figure it out.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[RhythmAddict112]

This should do it... Thanks for all your help!

CREATE TABLE [fts_test] (
	[FirstName] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[LastName] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[MiddleName] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[HomeAddressCity] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[JobTitle] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[fts_id] [int] IDENTITY (1, 1) NOT NULL ,
	CONSTRAINT [PK_fts_test] PRIMARY KEY  CLUSTERED 
	(
		[fts_id]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO

EXEC sp_fulltext_database  'enable'
EXEC sp_fulltext_catalog   'MyCatalog','create'


EXEC sp_fulltext_table     'fts_test', 'create', 'MyCatalog', 'pk_fts_test'

EXEC sp_fulltext_column    'fts_test', 'FirstName', 'add'
EXEC sp_fulltext_column    'fts_test', 'LastName', 'add'
EXEC sp_fulltext_column    'fts_test', 'JobTitle', 'add'
EXEC sp_fulltext_column    'fts_test', 'MiddleName', 'add'
EXEC sp_fulltext_column    'fts_test', 'HomeAddressCity', 'add'

EXEC sp_fulltext_table     'fts_test','activate' 

EXEC sp_fulltext_catalog   'MyCatalog', 'start_full'

http://geekswithblogs.net/sanjayu/

 

[mrdenny]

I forgot to ask, SQL 2000 or SQL 2005?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[RhythmAddict112]

SQL 2000..thanks again!

http://geekswithblogs.net/sanjayu/

 

[mrdenny]

I took your code and I'm not getting anything back. I'm using your code exactly. When I load data into the table and repopulate the full text index I'm still showing 0 rows in the full text index.

I'm not sure what the problem here is.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[RhythmAddict112]

Wow, that's just bizarre. Am I just blatantly missing something here? Anyone have some code I can run in my environment to build a table/index/fts? Would love to get to the bottom of this.

http://geekswithblogs.net/sanjayu/