Full text searches and/or CONTAINS in MS SQL 2005

Hiya,

I have recently become responsible for a small database for a volunteer soccer league. I am reasonably savvy when it comes to development, but I have not had a lot of experience with administration before.

I need to do what I think must be pretty simple: set up full text indexing so I can use a CONTAINS search on a table. The table contains all of the fields the kids use, and each field has a number of divisions that typically play on that field; we use these 'favored divisions' to make scheduling a little easier. Now, one day when I have time, I will set up a proper, normalized, one-to-many relationship between the favored divisions and the playing fields, but right now it's basically like this:

fieldID (int, primary key, identity seed)
fieldName (varchar), e.g. High School Field
favored_divisions (varchar) - comma-delimited list of divisions, e.g. G10,B14,G12

I imagine it's probably database sacrilege to have a comma-delimited list like that, but we don't have the resources now to re-write that piece of the web application. My question is, in SQL Server 2005, what do I need to do to be able to do a full-text search on this field with the following query:

SELECT fieldID, fieldName
FROM playing_fields
WHERE CONTAINS(favored_divisions,'G10')

Right now the query runs and does not return an error, but does not return any results, either. IIRC, full-text indexing is enabled by default in SQL Server 2005, but I am not familiar with the procedure -- something about having to populate a catalog. Do I need to edit or set up a new index on the actual playing_fields table? What has to happen to make this work?

Thanks very much,
Sam

Sam,

You do not need to use FTS for this. Simply use Charindex() or Like should do.

e.g.
SELECT fieldID, fieldName
FROM playing_fields
WHERE charindex(favored_divisions,'G10')<>0
--or
SELECT fieldID, fieldName
FROM playing_fields
WHERE favored_divisions LIKE '%G10%'

|||

You probably need to populate the index:

drop table playing_fields
go
create table playing_fields
(
fieldID int constraint PKplaying_fields primary key,
fieldName varchar(10) unique,
favored_divisions varchar(1000)
)
go
insert into playing_fields
select 1, 'one','G10,A01,B11'
union all
select 2, 'two','G12,A19,B21'
union all
select 3, 'three','G10,A21,B41'
union all
select 4, 'four','G03,A14,B11'
union all
select 5, 'Five','G02,A13,B01'
go

CREATE FULLTEXT CATALOG MyFirstFullText
IN PATH 'c:\mssql\data'
AS DEFAULT
AUTHORIZATION dbo
go
CREATE FULLTEXT INDEX ON playing_fields
(favored_divisions )
KEY INDEX PKplaying_fields
ON MyFirstFullText
WITH CHANGE_TRACKING OFF, NO POPULATION --the no population would be the thing that could cause this,
--though the default is to build the index.
go
SELECT fieldID, fieldName
FROM playing_fields
WHERE CONTAINS(favored_divisions,'G10')

--no results
go

ALTER FULLTEXT INDEX ON playing_fields
START FULL POPULATION

SELECT fieldID, fieldName
FROM playing_fields
WHERE CONTAINS(favored_divisions,'G10')

Returns:

fieldID fieldName
-- -
1 one
3 three

Note too that If you are only using it for the comma delimited list (and yes, a list like this is sacrilege :) and the amount of data is reasonable, just use a like:

SELECT fieldID, fieldName
FROM playing_fields
WHERE ',' + favored_divisions + ',' like '%,G10,%'

This also returns:

fieldID fieldName
-- -
1 one
3 three

This will require a table scan, but I will bet your set is probably small enough for this, depending on how large the league is :)

|||

Re:

SELECT fieldID, fieldName
FROM playing_fields
WHERE ',' + favored_divisions + ',' like '%,G10,%'

Clever use of where, I'd never thought of that. But then I guess if I were anything like a hardcore DBA, I wouldn't have such an offensive list in a table anyway!

These both look like good solutions. Probably no point in setting up a whole FTS if I'm ultimately going to rewrite this as a proper table anyway. Thank you both!

sql