Full text search - 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

Author

Topic

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2007-01-24 : 06:37:42

I'm just discovering full text search.

I have a question how to solve a problem.

I have a table Person
Id, FullName, PlaceOfBirth, SocialSecurityNumber, City
Which are part of FTS catalog.

Now I want to do a search

If I do

SELECT * FROM tblperson WHERE CONTAINS(* ,'"Henri" AND "Amsterdam"')

It returns no rows because Henri and Amsterdam are not part of one column.

OF course I could do
WHERE CONTAINS (FullName,'"Henri" OR "Amsterdam") OR CONTAINS (PlaceOfBirth,'"Henri" OR "Amsterdam") etc..

But that leaves me with more records that I want. I want the records where Henri and Amsterdam is found in one row.

Henri
~~~~
There's no place like 127.0.0.1

evjo
Starting Member

20 Posts

Posted - 2007-01-25 : 04:33:53

Do it as a join or where exists:

SELECT p1.*
FROM tblperson p1
WHERE CONTAINS(FullName,'"Henri" OR "Amsterdam"')
AND EXISTS
(
SELECT *
FROM tblperson p2
WHERE CONTAINS(PlaceOfBirth ,'"Henri" OR "Amsterdam"')
AND p1.ID = p2.ID
)

Whenever I see an old lady slip and fall on a wet sidewalk, my first instinct is to laugh. But then I think, what if I was an ant, and she fell on me. Then it wouldn't seem quite so funny.

- Jack Handey

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2007-01-25 : 05:00:17

Thx Jack, for this example it works.

Thing is I don't know where the keywords are related to. FullName and PlaceOfBirth are two fields. I don't know if the words contain a FullName part and a PlaceOfBirth part. So if there two search words and I use a AND EXISTS and check on SocialSecurity number that would give me a empty result.

I was already making triggers and meta-data tables to put all string in one text field, relate that to the same ID as tblPerson and Full Text Search index it. That works, but it would be too much maintainance since there a lot of tables to be searched.

What I want is that when I search in a table with 100.000 people I want to return only records with returnrows where all of the words in the search string are found. in one ore more of the fields.

Nevertheless, thx for your response!

Henri
~~~~
There's no place like 127.0.0.1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources