Mutiple keywords (or keyphrases) in SQL Server FTS

Question

0.00/5 (No votes)

See more:

Hi
I'm having trouble finding any information on how to use multiple keywords and / or combination of keyphrases in a SQL Server 2008 full-text search query using CONTAINS and FormsOf - INFLECTIONAL

I've tried :

SQL

USE tblCand
 SELECT CandID, LastName, FirstName  from [tblCand]
 WHERE CONTAINS(([Resume]),'FORMSOF(INFLECTIONAL,"Windows Administrator") AND FORMSOF(INFLECTIONAL,"Huntsville")');

yet this gives me no results.

But when I try :

SQL

USE tblCand
DECLARE @Keywords AS varchar(4000)
SET @Keywords = '"Windows Administrator" and Huntsville'
 SELECT CandID, LastName, FirstName  from [tblCand]
 WHERE CONTAINS(([Resume]),'FORMSOF(INFLECTIONAL,@Keywords)');

then this gives me all rows containing either "Windows Administrator" or Huntsville.

What I'm trying to get are just the rows that contain both the phrase "Windows Administrator" and the word Huntsville, both in the same row.

Any clues as to where I'm going wrong? Thanks, PTV

Posted 29-Jun-11 12:08pm

PaulVeeVee

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

JOAT-MON · Accepted Answer · 2011-06-29T13:21:00

Solution 1

It doesn't seem like the CONTAINS likes multiple FORMSOF phrases. Try the following:

SQL

USE tblCand;
   SELECT
      CandID, LastName, FirstName
   FROM
      [tblCand]
   WHERE
      CONTAINS(([Resume]), 'FORMSOF(INFLECTIONAL,"Windows Administrator" AND Huntsville)')

[EDIT] Or maybe it just doesn't like having two FORMSOF in the same token, so break them up:

SQL

USE tblCand;
   SELECT 
      CandID, LastName, FirstName
   FROM 
      [tblCand]
   WHERE 
      CONTAINS(
         ([Resume]),
         'FORMSOF(INFLECTIONAL, "Windows Administrator")' AND 
         'FORMSOF(INFLECTIONAL, Huntsville)'
      );

[EDIT2] Let's try breaking the CONTAINS into two phrases:

SQL

USE tblCand;
   SELECT
      CandID, LastName, FirstName
   FROM
      [tblCand]
   WHERE
      CONTAINS(([Resume]), 'FORMSOF(INFLECTIONAL, "Windows Administrator")') AND
      CONTAINS(([Resume]), 'FORMSOF(INFLECTIONAL, "Huntsville")');

Posted 29-Jun-11 13:21pm

JOAT-MON

Updated 29-Jun-11 18:40pm

v4

Comments

PaulVeeVee 29-Jun-11 23:36pm

Hi JOAT-MON ...thanks for the message. I tried that and received the following error :
Msg 7630, Level 15, State 3, Line 2
Syntax error near 'AND' in the full-text search condition 'FORMSOF(INFLECTIONAL,"Windows Administrator" AND Huntsville)'.

Any other thoughts? Thanks!

JOAT-MON 29-Jun-11 23:51pm

Maybe the issue is having the AND inside the token...see my [EDIT] above.

PaulVeeVee 30-Jun-11 0:15am

Hi JOAT-MON ... I really appreciate your trying to help. Unfortunately on that version I get this :
Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'AND'.
An expression of non-boolean type specified in a context where a condition is expected
(the whole section ---AND 'FORMSOF(INFLECTIONAL, Huntsville)');--- has the red squiggly line under it, so I could tell it wasn't liking it before I tried to Execute).
Any other tries? Thanks!

JOAT-MON 30-Jun-11 0:37am

Let try a couple other things:
1) try wrapping Huntsville in double quotes in the first two samples above
2) try breaking up the CONTAINS into two phrases...see [EDIT2]

PaulVeeVee 30-Jun-11 0:53am

Hi JOAT-MON
That worked!! I have to admit, and I'm very embarrased, but I think my original first block of code also works, but your last one did too. It was my mistake as I kept getting no results when my data may have actually changed where it didn't have that combination of phrase / word. Again, thank you so much for all of your help!!

PTV

JOAT-MON 30-Jun-11 1:02am

Glad to hear it! Well, if nothing else, we know a bunch of ways it doesn't work now. :)