I have a CONTAINS search on a full-text index and I'm having trouble running
it on multiple terms without using an operator like AND or OR.

Here is my search query:

SELECT  id
FROM  table1
WHERE CONTAINS(column1,@keywords)


These work as they should:

declare @keywords varchar(25)
set @keywords = ' "word1 and word2" '

declare @keywords varchar(25)
set @keywords = ' "word1 or word2" '


....and this:

declare @keywords varchar(25)
set @keywords = ' "word1 word2" '

yields an error:

Syntax error occurred near 'failure'. Expected ''''' in search condition

Help!


_____
DC G

I don't get this syntax error in SQL2000 or SQL2005. Which version are you
running?

Show quoteHide quote

DC G,
You need to use the double quotes correctly... See KB article 246800
(Q246800) "INF: Correctly Parsing Quotation Marks in FTS Queries" at
http://support.microsoft.com//default.aspx?scid=kb;EN-US;246800 for FTS
query examples without an @variable...

I tried the following query using the pubs table pub_info with the
FT-enabled and populated column pr_info

declare @keywords varchar(25)
set @keywords = ' "word1 word2" '
SELECT  pub_id, pr_info
from pub_info where contains(pr_info,@keywords)
/* -- expected results as word1 or word2 does not exist it this column
pub_id pr_info
------ -------------


(0 row(s) affected)
*/

I tested this on a Win2003 Server with SQL Server 2000 installed. Could you
post the full output of SELECT @@version as well as FT-enable the  pubs
table pub_info and run a Full population and test the above query?

Thanks,
John
Show quoteHide quote

Your use of double quotes appears to be incorrect. Everything contained
inside double quotes is classed as a phrase. Other comments are inline
below.

DC wrote  on Fri, 12 Aug 2005 16:43:10 -0400:

This is looking for the phrase "word1 and word2" in the text. If you want to
find everything that contains both words, you would use

set @keywords = ' "word1" and "word2" '

This will search for the phrase "word1 or word2". To find all rows that
contain either word, change it to

set @keywords = ' "word1" or "word2" '

This will search for the phrase "word1 word2". Is that what you wanted?

Any chance of posting the exact SQL that is being executed here, because it
isn't a search for "word1 word2".

Dan

Daniel,

My SQL is as you see it there:

declare @keywords varchar(25)
set @keywords = '"word 1 and word 2"'

SELECT id
FROM table1
WHERE CONTAINS(column1,@keywords)

I have seen this article:
http://support.microsoft.com//default.aspx?scid=kb;EN-US;246800

....but how can I parse this according to the rules of the KB article but
using T-SQL rather than the VB?

_____
DC G


Show quoteHide quote

DC Gringo,
Checkout this article
http://www.sqlservercentral.com/columnists/ckempster/fulltextindexingtextparsingroutine.asp
it should meet your needs with a bit of customization.

Regards,
John
Show quoteHide quote

John,

Ok, I tried that but some work and some don't and generate the error:

Syntax error occurred near ')'. Expected '_STRING, _PREFIX_STRING' in search
condition '" coping strategies " OR ("coping" near ) OR ("coping*" near ) OR
(FORMSOF(INFLECTIONAL,"coping") )'.


Here are my summary results and failing example code using various forms of
input of multiple keywords/phrases: "coping strategies":

These work:
coping strategies
coping and strategies
coping or strategies

These do not work:
"coping strategies"
"coping" and "strategies"
"coping" or "strategies"


Here's an example of failing test code:

Declare @keywords nvarchar(500)
declare @p_searchstring nvarchar(500)
declare @ftisenabled smallint
declare @ftop nvarchar(35)

set @keywords = '"coping strategies"'
set @p_searchstring = @keywords
set @ftisenabled = 1     -- 1 is YES, we are using Full Text Indexing,
essential if catalogs are down/unavailable

if @ftisenabled = 1
begin
    set @ftop = 'CONTAINS'    -- so we can quickly move between different
FTI operations for testing

    if ltrim(rtrim(@p_searchstring)) <> ''
    begin
        set @p_searchstring= replace(@p_searchstring, '%', '')
        exec FTI_FixString @p_searchstring, 'near', @p_searchstring OUTPUT
    end
else
    set @ftisenabled = 0
end

SELECT id
FROM table1
WHERE contains(column1,@p_searchstring)


_____
DC G



Show quoteHide quote

DC wrote  on Mon, 15 Aug 2005 14:48:15 -0400:

Show quoteHide quoteYou have misunderstood the parsing process. Here's the process as it is
written, taking the following string

word1 and word2

Replace all quotes:  word1 and word2
(no change as there are no quotes to remove)

Surround instances of 'and' or 'and not' with quotes: word1" and "word2
(as you can see, the quotes appear either side of the and)

Surround instances of 'or' or 'or not' with quotes: word1" and "word2
(no change as these terms are not in the string)

Surround the entire string with quotes: "word1" and "word2"
(as you can see, the words are quoted, the and is not included within the
result)


If you modify the VBScript code in the article as follows (all I have done
is make it pop up the result in a msgbox dialog) and save it as a .vbs file
and run it, you'll see the correct result:

      Dim strIn, RegEx, sInput

      sInput = "word1 and word2"

      strIn = sInput
      Set RegEx = New RegExp

      If Len(strIn) < 1 Then
            MsgBox ("You must enter a search string")
      Else
            strIn = Replace(strIn, Chr(34), "")
            If (InStr(strIn, "formsof") > 0) Or (InStr(strIn, "near") > 0)
Or (InStr(strIn, "isabout") > 0) Then
                  msgbox strIn
            Else
                  RegEx.IgnoreCase = True
                  RegEx.Global = True
                  RegEx.Pattern = "( and not | and )"
                  strIn = RegEx.Replace(strIn, Chr(34) & "$1" & Chr(34))
                  RegEx.Pattern = "( or not | or )"
                  strIn = RegEx.Replace(strIn, Chr(34) & "$1" & Chr(34))
                  strIn = Chr(34) & strIn & Chr(34)
                  msgbox  strIn
            End If
      End If


As you can see I have inserted your search terms in the strInput string.
When you run this, it will display a msgbox with

"word1" and "word2"

which is the correct quoting for requesting FTS to search for any rows with
both the words "word1" and "word2" in the column. If you use "word1 and
word2" then it will do a phrase search on that entire string, and not use
the "and" as a boolean term. Read the BOL for more details on this.

In T-SQL you could so something like this (it's a bit kludgy, so forgive me
if it's not the most efficient way of doing this)


declare @searchterms nvarchar(25)
set @searchterms = 'formsof(wibble)'

/*remove all quotes*/
set @searchterms = replace(@searchterms,char(34),'')

/*check for formsof, near, isabout*/
if charindex ('formsof',@searchterms) = 0 and charindex
('near',@searchterms) = 0 and charindex ('isabout',@searchterms) = 0
begin
   /*quotes around 'and'*/
   set @searchterms = replace(@searchterms,' and ','" and "')
   /*fix quotes in the middle of 'and not'*/
   set @searchterms = replace(@searchterms,' and "not ',' and not "')
   /*quotes around 'or'*/
   set @searchterms = replace(@searchterms,' or ','" or "')
   /*fix quotes in middle of 'or not'*/
   set @searchterms = replace(@searchterms,' or "not ',' or not "')
   /*add quotes around entire string*/
   set @searchterms = char(34) + @searchterms + char(34)
end

/*print the result*/
print @searchterms



Dan

I smell a big, fat compiler bug
How do you rate this script
SET ROWCOUNT!
Number of transactions
Squeezing out more performance!!